Access duplicates query

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

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/...UBED1=ACCESS-L





"mehstg1319" wrote in message
...
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

On 16 Dec, 15:52, "Roger Carlson"
wrote:
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/...UBED1=ACCESS-L

"mehstg1319" wrote in message

...



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

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

mehstg1319 wrote:
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