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
  • 2
  • 2
LVL 34

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 34

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now