Count number of unique values from a query

to get a count of unique PoN

SELECT Count(A.PoN) AS CountOfPoN
FROM (SELECT PoN
FROM YourQuery
GROUP BY PoN
) AS A;

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")

This will list the unique list of PoN with the count of each.

select PoN,count(PoN) as cnt from mytable
group by PoN

Select allOpen in new window

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.

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.

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?

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;

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;

that is the problem when names are NOT posted correctly

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;

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_#","qryUniquePONcounts")

When I run the report I get #Error in the field.

???

=DLookUp("[CountOfPPo_#]","qryUniquePONcounts")

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