By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,306 Members | 849 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,306 IT Pros & Developers. It's quick & easy.

Need help with eliminating duplicate entries in a query.

P: 3
I am pretty new at Access, but below is the code for my query. I am trying to obtain the most current meeting/contact date and its details for each meeting/contact. A few of the companies show up several times on the query even though I have chosen to "group by" company name. It works correctly on some but not others.

Expand|Select|Wrap|Line Numbers
  1. SELECT Max([Meetings/Contacts].[Meeting Date]) 
  2. AS [MaxOfMeeting Date], [Meetings/Contacts].[Company Name], [Meetings/Contacts].[Meeting  Time], [Meetings/Contacts].[Meeting Location], [Meetings/Contacts].[Our Contact], [Meetings/Contacts].[Met/Conversation With], [Meetings/Contacts].[Meeting Summary]
  3. FROM [Meetings/Contacts]
  4. GROUP BY [Meetings/Contacts].[Company Name], [Meetings/Contacts].[Meeting  Time], [Meetings/Contacts].[Meeting Location], [Meetings/Contacts].[Our Contact], [Meetings/Contacts].[Met/Conversation With], [Meetings/Contacts].[Meeting Summary]
  5. ORDER BY [Meetings/Contacts].[Company Name];
May 24 '13 #1

✓ answered by Rabbit

That's because you're also grouping by [Meetings/Contacts].[Meeting Time], [Meetings/Contacts].[Meeting Location], [Meetings/Contacts].[Our Contact], [Meetings/Contacts].[Met/Conversation With], [Meetings/Contacts].[Meeting Summary]. Grouping means that it's going to summarize on each distinct value combination of the group by fields.

What you need to do is drop all those other group by fields and get just the max date for each company. You then join that back to the table on the company and date to get the details of just those meetings.

Share this Question
Share on Google+
5 Replies

Rabbit
Expert Mod 10K+
P: 12,434
That's because you're also grouping by [Meetings/Contacts].[Meeting Time], [Meetings/Contacts].[Meeting Location], [Meetings/Contacts].[Our Contact], [Meetings/Contacts].[Met/Conversation With], [Meetings/Contacts].[Meeting Summary]. Grouping means that it's going to summarize on each distinct value combination of the group by fields.

What you need to do is drop all those other group by fields and get just the max date for each company. You then join that back to the table on the company and date to get the details of just those meetings.
May 24 '13 #2

P: 3
That worked, but now I'm in design view of the query and have added the meetings/contacts table. How do I join a query to a table?
May 24 '13 #3

Rabbit
Expert Mod 10K+
P: 12,434
The same way you join a table to a table.
May 24 '13 #4

P: 3
Think I've got it now. Thanks sooo much for your help!!
May 24 '13 #5

Rabbit
Expert Mod 10K+
P: 12,434
Not a problem, good luck with the reset of your project!
May 24 '13 #6

Post your reply

Sign in to post your reply or Sign up for a free account.