SQL Access - need query that displays distinct rows

Posted on 2014-09-04
Last Modified: 2014-09-04
I am using MS Access and need to run a query that selects distinct rows grouped by three fields: code, location, and date.  However, I also want to display a fourth field called [value].  Right now I am using  DISTINCT and ORDER BY commands but they return only those fields in the select DISTINCT command line.  And if I add all four fields to the DISTINCT line then I end up with duplicate instances of [code, location, and date] because the [value] field causes it to have additional distinct/unique rows.  

How can I show truly distinct/unique [code, location, and date] while also showing [value] for those unique [code, location, and date] rows?  (I realize it may simply display the first instance of the unique [code, location, and date] row, so I'd like it to average the [value] field, which is a number, from any instances of that unique set of [code, location, and date] rows.    

Here is my incorrect query:

SELECT DISTINCT code, location, date, value
FROM mytable
ORDER BY code, location, date;

This gives me multiple duplicate rows containing the same [code, location, date] combination (along with different [value] amounts.  Instead, I want only one unique instance of [code, location, and date], along with one averaged value of [value].
Question by:Cam Raben
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 37

Expert Comment

ID: 40304599
Instead of using the distinct keyword, create a Totals query.

Create a select query that selects the columns you want to see.
Press the large sigma button in the ribbon.  Access adds a "totals" row to the QBE grid and adds "group by" for all selected columns.
Change the "4th" column from group by to average or sum or whatever other aggregate makes sense for your data.

Author Comment

by:Cam Raben
ID: 40304616
Sigma sign is not visible.
LVL 14

Expert Comment

ID: 40304699
DISTINCT is essentially the same as GROUP BY, so try this version:

SELECT code, location, date, value
FROM mytable
GROUP BY code, location, date
ORDER BY code, location, date;
LVL 37

Accepted Solution

PatHartman earned 500 total points
ID: 40304740
Open the QBE and select the columns you want.  Then press the sigma button to convert to a totals query.

press Sigma to create Totals queryTo code it yourself, you need to specify how you want value to be aggregated if you are not going to group by it.

SELECT code, location, date, Avg(value)
 FROM mytable
 GROUP BY code, location, date
 ORDER BY code, location, date;

Author Closing Comment

by:Cam Raben
ID: 40304815
Thanks very much.   It works.

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question