• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 166
  • Last Modified:

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")
0
SteveL13
Asked:
SteveL13
  • 7
  • 7
  • 2
  • +1
1 Solution
 
Rey Obrero (Capricorn1)Commented:
to get a count of unique PoN

SELECT Count(A.PoN) AS CountOfPoN
FROM (SELECT PoN
FROM YourQuery
GROUP BY PoN
) AS A;
0
 
SteveL13Author Commented:
But how do I get that in the field's value on the report?  VBA code of some kind?
0
 
Rey Obrero (Capricorn1)Commented:
save the query as qryPoNCount   or whatever name you want

then just do a lookup

=dlookup("CountOfPoN", "qryPoNCount")
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jeff DarlingDeveloper AnalystCommented:
This will list the unique list of PoN with the count of each.

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

Open in new window

0
 
SteveL13Author Commented:
Now I just found out the field has to be in a group header.  ?????
0
 
PatHartmanCommented:
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.
0
 
SteveL13Author Commented:
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.
0
 
PatHartmanCommented:
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?
0
 
SteveL13Author Commented:
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;
0
 
Rey Obrero (Capricorn1)Commented:
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;
0
 
Rey Obrero (Capricorn1)Commented:
that is the problem when names are NOT posted correctly
0
 
SteveL13Author Commented:
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;
0
 
Rey Obrero (Capricorn1)Commented:
SELECT Count(A.[PPo_#]) AS [CountOfPPo_#]
    FROM (SELECT [PPo_#]
    FROM [qry_byOperator - Draw Machine]
    GROUP BY [PPo_#]
    ) AS A;
0
 
SteveL13Author Commented:
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.

???
0
 
Rey Obrero (Capricorn1)Commented:
=DLookUp("[CountOfPPo_#]","qryUniquePONcounts")
0
 
SteveL13Author Commented:
Getting close I think.  But the field is displaying the total not the group total.
0
 
Rey Obrero (Capricorn1)Commented:
I think the original  question that you posted has been answered.

post another q and give a detail information
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 7
  • 7
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now