Help with this query to pick only unique records.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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]));
 
Jay said:
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 said:
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 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)
 
Neil,

Thanks for your help.

The second post worked and my report is working.

Thanks, Again
 
Back
Top