Access duplicates query

[mehstg1319]

Hi people

I have an access query I am trying to implement in a database....due
to the nature of the data it is selecting...there will be duplicate
entries (the data is for a series of events and finds people who have
visited the events. Therefore, if a person has visited more than one
event, they will show more than once).

Basically, I am trying to produce a query to give me all the email
addresses in the dataset, without duplicates...but I cannot get it to
work for love nor money....I understand that it could possibly be done
using the GROUP BY command....but when I try that, I get all kinds of
aggregation errors.

The query is as follows:
-----------------------------------------------------------------------------------------------------------------------------
SELECT TblMember.[preferred email], tblworkshops.WorkshopTitle,
Tblmember.title+' '+Tblmember.firstname+' '+Tblmember.surname AS
[Member Name]
FROM TblMember, Tblbooking, tblworkshops
WHERE (((tblworkshops.WorkshopDate) Between #1/1/2001# And
#1/20/2008#) AND ((TblMember.TrueMember)=Yes) AND ((TblMember.memberid)
=[tblbooking].[memberid]) AND ((Tblbooking.workshopid)=[tblworkshops].
[workshopid]) AND ((tblworkshops.Wednesday)=Yes));

--------------------------------------------------------------------------------------------------------------------------------

Hopefully someone can help me.

Regards

Paul

 

[Roger Carlson]

Have you tried adding the DISTINCT predicate?

SELECT DISTINCT TblMember.[preferred email], tblworkshops.WorkshopTitle,...


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[mehstg1319]

Have you tried adding the DISTINCT predicate?

SELECT DISTINCT TblMember.[preferred email], tblworkshops.WorkshopTitle,....

--
--Roger Carlson
  MS Access MVP
  Access Database Samples:www.rogersaccesslibrary.com
  Want answers to your Access questions in your Email?
  Free subscription:
 http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi people

I have an access query I am trying to implement in a database....due
to the nature of the data it is selecting...there will be duplicate
entries (the data is for a series of events and finds people who have
visited the events. Therefore, if a person has visited more than one
event, they will show more than once).

Basically, I am trying to produce a query to give me all the email
addresses in the dataset, without duplicates...but I cannot get it to
work for love nor money....I understand that it could possibly be done
using the GROUP BY command....but when I try that, I get all kinds of
aggregation errors.

The query is as follows:
---------------------------------------------------------------------------­--------------------------------------------------
SELECT TblMember.[preferred email], tblworkshops.WorkshopTitle,
Tblmember.title+' '+Tblmember.firstname+' '+Tblmember.surname AS
[Member Name]
FROM TblMember, Tblbooking, tblworkshops
WHERE (((tblworkshops.WorkshopDate) Between #1/1/2001# And
#1/20/2008#) AND ((TblMember.TrueMember)=Yes) AND ((TblMember.memberid)
=[tblbooking].[memberid]) AND ((Tblbooking.workshopid)=[tblworkshops].
[workshopid]) AND ((tblworkshops.Wednesday)=Yes));

---------------------------------------------------------------------------­-----------------------------------------------------

Hopefully someone can help me.

Regards

Paul- Hide quoted text -

- Show quoted text -

Doesnt seem to make any difference.....have changed it to read:
SELECT DISTINCT TblMember.[preferred email],
tblworkshops.WorkshopTitle, Tblmember.title+' '+Tblmember.firstname+'
'+Tblmember.surname AS [Member Name]


Paul

 

[John Spencer]

You have to remove the tblworkshops.WorkshopTitle from the list of returned
fields. Otherwise you are going to get one row for each combo of member and
workshop.

I would write that SQL as follows:

SELECT DISTINCT TblMember.[preferred email]
, (Tblmember.title + ' ') & (Tblmember.firstname + ' ') & Tblmember.surname AS
[Member Name]
FROM (tblMember INNER JOIN tblBooking
ON tblMember.MemberID = tblBooking.MemberID)
INNER JOIN TblWorkshops
ON tblBooking.worksshopId = tblWorkshops.WorkShopId
WHERE tblworkshops.WorkshopDate Between #1/1/2001# And
#1/20/2008#
AND TblMember.TrueMember=Yes
AND tblworkshops.Wednesday=Yes


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County