Solved

SQL Access - need query that displays distinct rows

Posted on 2014-09-04
5
489 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 35

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 35

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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.
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…

809 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