Access duplicates query

  • Thread starter Thread starter mehstg1319
  • Start date Start date
M

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
 
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
Click to expand...
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).
Click to expand...
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.
Click to expand...
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));
---------------------------------------------------------------------------­-----------------------------------------------------
Click to expand...

Hopefully someone can help me.
Regards
Click to expand...

Paul- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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
 
Back
Top