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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.