Suppressing Duplicates

[Leathem]

I have a table listing organization members and the various trips they have signed up for and would like to make mailing labels based on that list. The problem is that any one member may have signed up for multiple trips, yet I'd only need one mailing label for that member. So a simple query produces multiple entries for some of the members, and the report based on that query produces multiple mailing labels for that member. How can I suppress the duplicates based on just some of the fields in the table, for example last and first names, or last name and address?

 

[jdraw]

Use SELECT DISTINCT
see:
http://www.w3schools.com/SQL/sql_distinct.asp

 

[Leathem]

I agree that that's the way to do it, but I'm unsure of the SQL syntax. There are a number of fields that have to be reported in addition to the member names. Can I restrict the DISTINCT instruction to just one or two fields?

 

[jdraw]

What exactly are the Tables involved?
And what exactly are the fields involved?
What will the Mailing Labels contain?

There may be a few ways to do what you want. Let's look at the data and requirements and then see if options are available.

 

[vbaInet]

I agree that that's the way to do it, but I'm unsure of the SQL syntax. There are a number of fields that have to be reported in addition to the member names. Can I restrict the DISTINCT instruction to just one or two fields?

But have you tried it out yet?

DISTINCT is the way to go if you INNER JOIN Members to the Trips table and only select fields from the Members table.

 

[Leathem]

Ok, I've attached the query and the two tables it draws data from. The query first looks at the enrollment table to find the names of the members enrolled (have expressed a preference number for a course) but do not have an email address, then obtains the address information from the member data table. The problem is that the enrollment table lists names multiple times, once for each course they have selected, yet I really only need one mailing label.

 

[vbaInet]

We still don't know whether or not you've tried Distinct though?

 

[Leathem]

Oh, sorry. I did try DISTINCT. I put it at the beginning of the SQL statement as in "SELECT DISTINCT" rather than just "SELECT". It eliminated one name, but left a number of duplicates.

 

[jdraw]

What exactly do you want in the address label?

You don't have to have (necessarily) every field involved in the query.

I reformatted the query sql

SELECT Enrollments.StudentName
, Enrollments.AL1
, Enrollments.AL2
, Nz([AL1],0)+Nz([AL2],0) AS Checksum
, IIf([Checksum]>0,"Y","N") AS Enrolled
, MemberData.[FIRST NAME]
, MemberData.[LAST NAME]
, MemberData.ADDRESS
, MemberData.CITY
, MemberData.ST
, MemberData.ZIP
, MemberData.[E-MAIL]
FROM Enrollments 
, MemberData 
WHERE 
Enrollments.StudentName = MemberData.Name
and 
 MemberData.[E-MAIL]  Is Null ;

It would seem that

FirstName LastName
Address
City St ZIP

would satisfy mailing requirement.
Since your WHERE statement only keeps records where email is null you don't need to SELECT it.


This sql may give you a reasonable mailing label

SELECT 
 MemberData.[FIRST NAME] & " " & MemberData.[LAST NAME]
, MemberData.ADDRESS
, MemberData.CITY & "  " &  MemberData.ST & " " & MemberData.ZIP

FROM Enrollments 
, MemberData 
WHERE 
Enrollments.StudentName = MemberData.Name
and 
 MemberData.[E-MAIL]  Is Null ;

I'm not sure what you wanted to do with

, Nz([AL1],0)+Nz([AL2],0) AS Checksum
, IIf([Checksum]>0,"Y","N") AS Enrolled

 

[Leathem]

Beautiful! I really like your last SQL statement; it does all that I needed to do for the mailing labels. I added DISTINCT to the SELECT statement and got rid of all the duplicates. Thank you so much!!

 

[jdraw]

Glad to help!!