Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Count number of unique values from a query

Posted on 2015-01-30
17
Medium Priority
?
161 Views
Last Modified: 2015-01-31
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
Comment
Question by:SteveL13
  • 7
  • 7
  • 2
  • +1
17 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40580348
to get a count of unique PoN

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

Author Comment

by:SteveL13
ID: 40580354
But how do I get that in the field's value on the report?  VBA code of some kind?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40580363
save the query as qryPoNCount   or whatever name you want

then just do a lookup

=dlookup("CountOfPoN", "qryPoNCount")
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 13

Expert Comment

by:Jeff Darling
ID: 40580372
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
 

Author Comment

by:SteveL13
ID: 40580742
Now I just found out the field has to be in a group header.  ?????
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 40580782
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
 

Author Comment

by:SteveL13
ID: 40581414
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
 
LVL 40

Expert Comment

by:PatHartman
ID: 40581491
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
 

Author Comment

by:SteveL13
ID: 40581537
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40581550
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40581551
that is the problem when names are NOT posted correctly
0
 

Author Comment

by:SteveL13
ID: 40581565
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 40581584
SELECT Count(A.[PPo_#]) AS [CountOfPPo_#]
    FROM (SELECT [PPo_#]
    FROM [qry_byOperator - Draw Machine]
    GROUP BY [PPo_#]
    ) AS A;
0
 

Author Comment

by:SteveL13
ID: 40581602
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40581603
=DLookUp("[CountOfPPo_#]","qryUniquePONcounts")
0
 

Author Comment

by:SteveL13
ID: 40581606
Getting close I think.  But the field is displaying the total not the group total.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40581612
I think the original  question that you posted has been answered.

post another q and give a detail information
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question