Solved

SQL Access - need query that displays distinct rows

Posted on 2014-09-04
5
491 Views
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].
0
Comment
Question by:Cam Raben
  • 2
  • 2
5 Comments
 
LVL 36

Expert Comment

by:PatHartman
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.
0
 

Author Comment

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

Expert Comment

by:ThomasMcA2
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;
0
 
LVL 36

Accepted Solution

by:
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;
0
 

Author Closing Comment

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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

821 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