I run a query from 3 tables: ORG, PRODUCT, SERVICE.
I first setup this one table and the query brings up the correct info using:
ORG.ORG_ID
ORG.ORGNAME
DATA EXAMPLE:
BestBuy
I add the next table, PRODUCT using the field PRODUCT:
PRODUCT.PRODUCT
The ORG_ID of both tables is automatically joined (option 1).
All the data comes up correct.
BestBuy TV
BestBuy Radio
Now I add the table SERVICE.
The ORG_ID of this table and ORG is automatically joined (option 1).
Without adding anything from SERVICE, my data is already duplicated.
BestBuy TV
BestBuy TV
BestBuy Radio
BestBuy Radio
Now I added in SERVICE.EXPDATE:
BestBuy TV 11/14/2000
BestBuy TV 2/28/2000
BestBuy Radio 11/14/2000
BestBuy Radio 2/28/2000
Obviously each product has its own exppire dtae but the query shows it more than once with each other’s date. If I have a site with 3 products having 3 seperate expire dates, it will show each product 3 times (9 total) each having 3 different product dates. With me?
Okay, so I created SERVICE.PRODUCT and added it in (does not get joined) so I can see which product has the correct expire date.
ORG. PRODUCT. SERVICE. SERVICE.
SITE PRODUCT EXPIREDATE PRODUCT
BestBuy TV 11/14/2000 TV
BestBuy TV 2/28/2000 Radio
BestBuy Radio 11/14/2000 TV
BestBuy Radio 2/28/2000 Radio
BestBuy’s TV does expire 11/14 and the Radio 2/28. As you can see, it adds each product’s expire date for each product listed (makes sense?).
If I was to join PRODUCT.PRODUCT and SERVICE.PRODUCT, it will eliminate any entries that do not match (some sites will have missing EXPDATE). If I tried option 2 or 3 in the join string, I get an error telling me to recreate a whole new query.
Any deas how I can fix all this to give me just this?:
SITE PRODUCT EXPIRE SERV.PRO
BestBuy TV 11/14/2000 TV
BestBuy Radio 2/28/2000 Radio
And eventually remove SERVICE.PRO