List all products which are made or packaged at a specific site.

[BC_Randall]

In Access 97: I have a site table and a product table. Each product has a field for manufacture site and a field for packaging site. I need to produce a report which lists all sites and their associated products. A product should only be listed once even if it's made and packaged at that site (but I need to indicate beside the product if it is made, packaged or both at that site). The site should be listed even if nothing is made or packaged there. Can anyone please help me set up the query(s) to accomplish this.

Thanks for all your help.... Brian

 

[araskas]

You have asked for a really complicated query. Here it is -
Assume your tables are
Site{ Sitename }
Product{PName, MSite, Pite }
I am mot using any SiteID or ProductID fields. I am just doing direct string comparision.
SELECT Site.SiteName,
Product.PName,
IIf([Site]![SiteName]=[Product]![MSite] And [Site]![SiteName]=[Product]![PSite],"Mfg & Packed",IIf([Site]![SiteName]=[Product]![MSite] And [Site]![SiteName]<>[Product]![PSite],"Mfg ",IIf([Site]![SiteName]<>[Product]![MSite] And [Site]![SiteName]=[Product]![PSite],"Packed"))) AS Activity

FROM Site, Product

WHERE ((([Site]![SiteName])=[Product]![MSite] Or ([Site]![SiteName])=[Product]![PSite]))

UNION ALL

SELECT Site.SiteName, "None", "No Activity"

FROM Site

WHERE (((Site.SiteName) Not In (SELECT MSite FROM Product ) And (Site.SiteName) Not In (SELECT PSite FROM Product )))
ORDER BY Site.SiteName;

Here is my test data :
SiteName
A
B
C
D
E
F
G
H


PName MSite PSite
PA A E
PB B C
PC A A
PD A A
PE A A
PF C B
PG B A