Count Unique Problem

[gcgoode]

Im building a query that right now has an output that looks something like this:

Local / Year /Sum / ID #
12 / 2014 / $450 / 4
12 / 2014 / 300 / 5
12 / 2015 / 400 / 4

That is with the ID # as group by. I want to only group by local and year and just have the ID # output count the unique entries in the database. So in that last example my output would instead look like

Local / Year / Sum / CountofID #
12 / 2014 / $750 / 2
12 / 2015 / 400 / 1


Ive tried the standard count function as well as a Count distinct function and still haven't gotten what I want.

Any pointers?

[JoeM]

Based on your query example, choosing these Total Row options for each field should return what you want:
Local: Group By
Year: Group By
Sum: Sum
ID #: Count

This was the SQL code I came up with:

SELECT Table1.Local, Table1.Year, Sum(Table1.Sum) AS SumOfSum, Count(Table1.[ID #]) AS [CountOfID #]
FROM Table1
GROUP BY Table1.Local, Table1.Year;

and it came out with the results you posted.

[JoeM]

Or is the issue really that you have data like this:

Local / Year /Sum / ID #
12 / 2014 / $450 / 4
12 / 2014 / 300 / 5
12 / 2015 / 400 / 4
12 / 2014 / 200 / 4

and you expect your results to look like this:
12 / 2014 / 950 / 2
12 / 2015 / 400 / 1

and NOT like this:
12 / 2014 / 950 / 3
12 / 2015 / 400 / 1

If that is the case, there are a few ways to do this. You can use two queries (or nest them into a single query, if you are comfortable doing that).

In your first query, you would do an Aggregate Query like this:
Local: Group By
Year: Group By
Sum: Sum
ID #: Group By

Then, you would create a second Aggregate Query based on the first one, where you do this:
Local: Group By
Year: Group By
Sum: Sum
ID #: Count

[gcgoode]

Yeah that was my first thought as well, but for some reason its not returning the expected results. I can compare it to my group by datasheet and see that it is wrong.

[gcgoode]

Oh, nevermind. I was messing myself up and forgot my Group by DatePart was changing around the results. Thanks for the help JoeM! And yes in that second post you outlined something I thought about but haven't tried yet, thanks for giving me the option.

[gcgoode]

Another quick question for you, something I've been wanting to fix about this for a while.

Say for locale 12 there is no data for 2015; instead of omitting it completely from the output can I get Acess to return:
12 / 2015 / 0 / 0
?

[JoeM]

An Access query cannot create a record out of something that does not exist. Aggregate Queries take a number of existing records, and collapses them down into a smaller number of records. You are actually talking about creating records. So if you do not have any records for 12 / 2015, it has nothing to collapse down from.

You could conceivably create a separate table that holds all your locale / year combinations, and try introducing/incorporating that into your queries to do something like that. Then Access would have a starting records to work from.

[gcgoode]

Ok, thats what I was thinking. Thanks for the help.

[JoeM]

Yes, you would essentially set up a master table that lists all possible locale/year combinations.
Then create a new query where you do a Left Join from this table to your final aggregate query, on both the locale and year fields.
Return the locale and year fields from the master table and the Sum/Count fields from your aggregate query.
To convert the Nulls in the Sum/Count fields for the records with no entries, just use the NZ function, i.e.
Nz([SumOfSum],0)+0

[June7]

Might be able to create the 'all possible combinations' dataset with query. Do you already have a table of locals?

Then if the data table has at least one record for each year, a query can pull the years from there.

SELECT DISTINCT [Year], tblLocals.ID FROM tblLocals, tblData;

Year is a reserved word. Should not use reserved words as names.