Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Cleveland USA

    Unanswered: How to count unique values in report?

    I have a report that gives a total at the bottom. I want it to give the number of unique items for a given field, not the overall total. For example:

    Apple Green
    Apple Yellow
    Apple Red
    Banana Yellow
    Banana Green
    Grapes Green

    When I count the number of fruits, I want the answer to be 3, not 6. How do I get this to happen in my report?

  2. #2
    Join Date
    Jul 2004
    The simplest way would be to produce a subreport based on a totals query that counts all the records for each unique item in your table.

    To create the query, add your table then add the field of 'Item'. Add any other field from your table, as long as you know there will be an entry in every record (for example, the ID field for the record)

    Right click on any of the two columns in the lower half of the window and choose Totals. In the country column, in the totals line, choose 'group by' and in the other field, choose count. Run the query to check it's working, then save.

    Base a report on this query and save. Add the report to your other report as a subreport, in the report header. Don't link it to other fields in your report.

  3. #3
    Join Date
    Dec 2004
    Madison, WI

    Counting totals

    Another way you could also try is have a query which sums the values. For example (adding in any other parameters in the query which match the reports source parameters):

    FType, CCount (which is the sum)
    Apple, 3
    Banana, 2
    Grapes, 1

    Then in your report, you'd have a text field (probably in the grouping footer) with the controlsource: = dlookup("[CCount]","[MySumQuery]","FType = '" & Reports!MyReport!FruitType & "'"

    FruitType would be the field on the report from your recordsource.

    It's a slower way of doing it because dlookups take longer and if you have a fairly large number of records, you may find the report will take a little longer.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts