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
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].