SQL Access - need query that displays distinct rows

Posted on 2014-09-04
Medium Priority
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 38

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 38

Accepted Solution

PatHartman earned 2000 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

TCP/IP Network Protocol Cheat Sheet

TCP/IP is a set of network protocols which is best known for connecting the machines that make up the Internet. The truth is that TCP/IP is one of the oldest network protocols and its survival is mainly based on its simplicity and universality.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

771 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