Help with this query to pick only unique records.

[Jay Simenson]

The query below works. How can I change it to display only one record for
each businessname. I am getting multiple records with the UNION.

SELECT Members.MemberID, Members.TollFreePhone, Members.PhysZip,
Members.PhysState, Members.PhysCity, Members.PhysAddress, Members.Website,
Members.BusinessPhone, Members.BusinessName, Members.Category1,
Members.Inactive, Category.CatID, Category.Category, Category.Brochure
FROM Category INNER JOIN Members ON Category.CatID = Members.Category1
WHERE (((Members.Inactive)=0) AND
((Category.Brochure)=[Forms]![GuideSelect2]![Combo0]));
UNION
SELECT Members.MemberID, Members.TollFreePhone, Members.PhysZip,
Members.PhysState, Members.PhysCity, Members.PhysAddress, Members.Website,
Members.BusinessPhone, Members.BusinessName, Members.Category1,
Members.Inactive, Category.CatID, Category.Category, Category.Brochure
FROM Category INNER JOIN Members ON Category.CatID = Members.Category2
WHERE (((Members.Inactive)=0) AND
((Category.Brochure)=[Forms]![GuideSelect2]![Combo0]));
union
SELECT Members.MemberID, Members.TollFreePhone, Members.PhysZip,
Members.PhysState, Members.PhysCity, Members.PhysAddress, Members.Website,
Members.BusinessPhone, Members.BusinessName, Members.Category1,
Members.Inactive, Category.CatID, Category.Category, Category.Brochure
FROM Category INNER JOIN Members ON Category.CatID = Members.Category3
WHERE (((Members.Inactive)=0) AND
((Category.Brochure)=[Forms]![GuideSelect2]![Combo0]));

 

[Neil Sunderland]

The query below works. How can I change it to display only one record for
each businessname. I am getting multiple records with the UNION.

Does this do what you want?

SELECT M.MemberID, M.TollFreePhone, M.PhysZip,
M.PhysState, M.PhysCity, M.PhysAddress,
M.Website, M.BusinessPhone, M.BusinessName,
M.Category1,
(SELECT CC1.Category FROM Category AS CC1
WHERE CB1.CatID = M.Category1) AS Cat1,
(SELECT CB1.Brochure FROM Category AS CB1
WHERE CB1.CatID = M.Category1) AS Brochure1,
M.Category2,
(SELECT CC2.Category FROM Category AS CC2
WHERE CB2.CatID = M.Category2) AS Cat2,
(SELECT CB2.Brochure FROM Category AS CB2
WHERE CB2.CatID = M.Category2) AS Brochure2,
M.Category3,
(SELECT CC3.Category FROM Category AS CC3
WHERE CB3.CatID = M.Category3) AS Cat3,
(SELECT CB3.Brochure FROM Category AS CB3
WHERE CB3.CatID = M.Category3) AS Brochure3,
M.Inactive
WHERE (((Members.Inactive)=0) AND
((Category.Brochure)=[Forms]![GuideSelect2]![Combo0]));

 

[Neil Sunderland]

Does this do what you want?

Don't bother, because it won't work. This *might*, now I've managed to
get more than eight brain cells functioning at the same time:

Create a new query that does this:

SELECT M.MemberID, M.TollFreePhone, M.PhysZip,
M.PhysState, M.PhysCity, M.PhysAddress,
M.Website, M.BusinessPhone, M.BusinessName,
M.Category1,
(SELECT CC1.Category FROM Category AS CC1
WHERE CB1.CatID = M.Category1) AS Cat1,
(SELECT CB1.Brochure FROM Category AS CB1
WHERE CB1.CatID = M.Category1) AS Brochure1,
M.Category2,
(SELECT CC2.Category FROM Category AS CC2
WHERE CB2.CatID = M.Category2) AS Cat2,
(SELECT CB2.Brochure FROM Category AS CB2
WHERE CB2.CatID = M.Category2) AS Brochure2,
M.Category3,
(SELECT CC3.Category FROM Category AS CC3
WHERE CB3.CatID = M.Category3) AS Cat3,
(SELECT CB3.Brochure FROM Category AS CB3
WHERE CB3.CatID = M.Category3) AS Brochure3,
M.Inactive
WHERE Members.Inactive=0

Then in your form (substituting NewQuery for the actual name of the
query you just created):

SELECT * FROM NewQuery
WHERE Brochure1 = [Forms]![GuideSelect2]![Combo0] OR
Brochure2 = [Forms]![GuideSelect2]![Combo0] OR
Brochure2 = [Forms]![GuideSelect2]![Combo0] OR

 

[Neil Sunderland]

Neil Sunderland wrote:

[Sorry - hit send too quickly...]

That last bit should be:

SELECT * FROM NewQuery
WHERE Brochure1 = [Forms]![GuideSelect2]![Combo0] OR
Brochure2 = [Forms]![GuideSelect2]![Combo0] OR
Brochure3 = [Forms]![GuideSelect2]![Combo0]

This should also work:

SELECT * FROM NewQuery
WHERE [Forms]![GuideSelect2]![Combo0]
IN (Brochure1, Brochure2, Brochure3)

 

[Jay Simenson]

Neil,

Thanks for your help.

The second post worked and my report is working.

Thanks, Again