SteveL13
asked on
Count number of unique values from a query
I have a report that has an unbound field. The report's record source is a query. In that queries detail is a field named PoN.
In that report field I want to show the number of unique PoN's there are in the query.
So something like this which of course isn't right:
= DCount UNIQUE ("[PoN]", "qryTestQuery")
In that report field I want to show the number of unique PoN's there are in the query.
So something like this which of course isn't right:
= DCount UNIQUE ("[PoN]", "qryTestQuery")
ASKER
But how do I get that in the field's value on the report? VBA code of some kind?
save the query as qryPoNCount or whatever name you want
then just do a lookup
=dlookup("CountOfPoN", "qryPoNCount")
then just do a lookup
=dlookup("CountOfPoN", "qryPoNCount")
This will list the unique list of PoN with the count of each.
select PoN,count(PoN) as cnt from mytable
group by PoN
ASKER
Now I just found out the field has to be in a group header. ?????
If the field is a group header, you can just add a control in the corresponding group footer:
=count(*)
And that will give you a distinct count.
=count(*)
And that will give you a distinct count.
ASKER
I probably didn't explain well enough. The report has a group. In the report's group footer I have a field that needs to show the number of unique (or is it distinct) values of PoN in that group.
So if in the detail section we have:
Baseball
Football
Basketball
Baseball
then the field needs to show 3.
So if in the detail section we have:
Baseball
Football
Basketball
Baseball
then the field needs to show 3.
Access SQL doesn't have a specific method to do this as SQL Server and other RDBMS' do so you have to improvise. You need a query that groups and counts the recordset used to populate the subreport and it needs to be filtered the same way.
Did you try the query posted by Rey?
Did you try the query posted by Rey?
ASKER
I'm trying this: but I get a syntax error on 'Count(A.PPo_#) '
(note the field is really PPo_#, not PoN as originally posted)
SELECT Count(A.PPo_#) AS CountOfPPo_#
FROM (SELECT PPo_#
FROM qry_byOperator - Draw Machine
GROUP BY PPo_#
) AS A;
(note the field is really PPo_#, not PoN as originally posted)
SELECT Count(A.PPo_#) AS CountOfPPo_#
FROM (SELECT PPo_#
FROM qry_byOperator - Draw Machine
GROUP BY PPo_#
) AS A;
that is the problem when names are posted correctly
SELECT Count(A.[PPo_#]) AS CountOfPPo_#
FROM (SELECT [PPo_#]
FROM [qry_byOperator - Draw Machine]
GROUP BY [PPo_#]
) AS A;
SELECT Count(A.[PPo_#]) AS CountOfPPo_#
FROM (SELECT [PPo_#]
FROM [qry_byOperator - Draw Machine]
GROUP BY [PPo_#]
) AS A;
that is the problem when names are NOT posted correctly
ASKER
Sorry. Now when I use this I get "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect"
SELECT Count(A.[PPo_#]) AS CountOfPPo_#
FROM (SELECT [PPo_#]
FROM [qry_byOperator - Draw Machine]
GROUP BY [PPo_#]
) AS A;
SELECT Count(A.[PPo_#]) AS CountOfPPo_#
FROM (SELECT [PPo_#]
FROM [qry_byOperator - Draw Machine]
GROUP BY [PPo_#]
) AS A;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Status:
I have created a new query named qryUniquePONcounts. The SQL for the new query is:
SELECT Count(A.[PPo_#]) AS [CountOfPPo_#]
FROM (SELECT [PPo_#] FROM [qry_byOperator - Draw Machine] GROUP BY [PPo_#]) AS A;
The in the footer section of the report I have an unbound field with this as the control source:
=DLookUp("CountOfPPo_#","q ryUniquePO Ncounts")
When I run the report I get #Error in the field.
???
I have created a new query named qryUniquePONcounts. The SQL for the new query is:
SELECT Count(A.[PPo_#]) AS [CountOfPPo_#]
FROM (SELECT [PPo_#] FROM [qry_byOperator - Draw Machine] GROUP BY [PPo_#]) AS A;
The in the footer section of the report I have an unbound field with this as the control source:
=DLookUp("CountOfPPo_#","q
When I run the report I get #Error in the field.
???
=DLookUp("[CountOfPPo_#]", "qryUnique PONcounts" )
ASKER
Getting close I think. But the field is displaying the total not the group total.
I think the original question that you posted has been answered.
post another q and give a detail information
post another q and give a detail information
SELECT Count(A.PoN) AS CountOfPoN
FROM (SELECT PoN
FROM YourQuery
GROUP BY PoN
) AS A;