How do I create a query that does not return duplicate records?

I am trying to get a list of home addresses for key sales reps (to send Christmas presents).  The reps I want are in a table called "tblOfficeContacts" which also contains a field I want called "ContactName".  The reps are assigned to various offices so their EMSRepID is repeated in the tblOfficeContacts table.
The reps are also included in a table called "RepsActive".  The EMSRepID is unique- the rep is only listed once in this table.  The RepsActive table contains the home address that I wish to extract. This table also includes a bunch of other reps that I don't want to select.
In other words, I want to select all the reps in the OfficeContacts table only once and get their home address from the RepsActive table.
No matter what I've tried, I'm unable to pull a list of reps from the tblOfficeContacts with their home addres from the RepsActive table without duplicating the rep records.   OfficeContactsAddress.accdb
thutchinsonAsked:
Who is Participating?
 
shambaladCommented:
OK - the GROUP BY suggestion was a 'quick and dirty' fix. The correct answer is to change your DISTINCTROW predicate to DISTINCT.

That is to say, your current SQL is:

SELECT DISTINCTROW RepsActive.EMSRepID, tblOfficeContacts.ContactName, RepsActive.FullName, RepsActive.RepAddr1, RepsActive.RepAddr2, RepsActive.RepCity, RepsActive.Zip
FROM tblOfficeContacts LEFT JOIN RepsActive ON tblOfficeContacts.EMSRepID = RepsActive.EMSRepID;

You should change it to:

SELECT DISTINCT RepsActive.EMSRepID, tblOfficeContacts.ContactName, RepsActive.FullName, RepsActive.RepAddr1, RepsActive.RepAddr2, RepsActive.RepCity, RepsActive.Zip
FROM tblOfficeContacts LEFT JOIN RepsActive ON tblOfficeContacts.EMSRepID = RepsActive.EMSRepID;

The following explanation is from page:
http://www.fmsinc.com/microsoftaccess/query/distinct_vs_distinctrow/unique_values_records.asp

DISTINCT checks only the fields listed in the SQL string and then eliminates the duplicate rows. Results of DISTINCT queries are not updateable. They are a snapshot of the data.

DISTINCT queries are similar to Summary or Totals queries (queries using a GROUP BY clause).

DISTINCTROW, on the other hand, checks all fields in the table that is being queried, and eliminates duplicates based on the entire record (not just the selected fields). Results of DISTINCTROW queries are updateable.
0
 
shambaladCommented:
Try grouping them:


SELECT DISTINCTROW RepsActive.EMSRepID, tblOfficeContacts.ContactName, RepsActive.FullName, RepsActive.RepAddr1, RepsActive.RepAddr2, RepsActive.RepCity, RepsActive.Zip
FROM tblOfficeContacts LEFT JOIN RepsActive ON tblOfficeContacts.EMSRepID = RepsActive.EMSRepID
GROUP BY RepsActive.EMSRepID, tblOfficeContacts.ContactName, RepsActive.FullName, RepsActive.RepAddr1, RepsActive.RepAddr2, RepsActive.RepCity, RepsActive.Zip;
0
 
thutchinsonAuthor Commented:
Thanks, shambalad.  I have to leave the office now for long commute home.  I will dial in and try this later tonight.  I'll let you know.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
calinutzCommented:
Have you tried this:

SELECT distinct contactname, RepAddr1
FROM tblOfficeContacts cont, RepsActive activ
where cont.EMSRepID = activ.EMSRepID
order by 1


?
Seems to be working to me...
0
 
thutchinsonAuthor Commented:
thanks to you also, calinutz.  I will try both ways.  I'm more interested in learning how I can repeat this in future cases than just getting this problem done.  Any explanations are appreciated.

I'll let you guys know how it goes later.
0
 
shambaladCommented:
Actually my last post is really an elaboration on calinutz's suggestion.
0
 
thutchinsonAuthor Commented:
Shambalad,

Thanks for your detailed explanation of the difference between DISTINCT and DISTINCTROW.

I will award you the points for leading me to this solution:
SELECT qrytblOfficeContacts_notAdmin.ContactName, RepsActive.FullName, RepsActive.RepAddr1, RepsActive.RepAddr2, RepsActive.RepCity, RepsActive.StateAbbr, RepsActive.Zip, qrytblOfficeContacts_notAdmin.ContactMemo
FROM qrytblOfficeContacts_notAdmin LEFT JOIN RepsActive ON qrytblOfficeContacts_notAdmin.EMSRepID = RepsActive.EMSRepID
GROUP BY qrytblOfficeContacts_notAdmin.ContactName, RepsActive.FullName, RepsActive.RepAddr1, RepsActive.RepAddr2, RepsActive.RepCity, RepsActive.StateAbbr, RepsActive.Zip, qrytblOfficeContacts_notAdmin.ContactMemo;

I couldn't quite translate the syntax of Calinutz' post although I'm quite sure it would have worked if I could have figured out what was missing.
Thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.