Timothy has attended:
Access Advanced course
Counting Discreet Text Values In a Report Q For Anthony
Hi Anthony,
I have been trying in vain to make a report behave how I want it to. What I am trying to achieve is take a column in the report. The data are all text values, in fact there are only four different values that appear in the column. What I want is to have each of the four discreet values counted and then shown at the bottom.
e.g
A=10
B=25
C=12
D=44
It sounds simple but has been anything but that. I have mucked around with pivot tables, parameter queries based off of list boxes, annoyingly the query doesn't like it when you change the list box to multi select to simple but none of this has achieved what I need. I had originally abandoned this idea but since I can't find any other way round it....
Tim
RE: Counting Discreet Text Values In a Report Q For Anthony
Hi Timothy, thanks for your query. I would perform two queries to achieve this. I would create one query, using the Expression Builder to count the values themselves. Then I would use a second query to create a recordset including those values with the source recordset of the record. Then I would run the report from that bespoke recordset. It can be tricky to do those sorts of calculations on the report but Microsoft have a walkthrough here:
http://office.microsoft.com/en-us/access-help/count-data-by-using-a-query-HA010096311.aspx
However, if you did want such a calculation on the report then I'd use the Count and IIF functions, as here:
http://www.databasedev.co.uk/count_values_countiif.html
Your version would count only those fields with an "A" for one total, and so on.
Hope this helps,
Anthony
RE: Counting Discreet Text Values In a Report Q For Anthony
Hi Anthony,
Now you mention the two query process I don't see how I didn't think of it before, it seems so simple. Unfortunately, it doesn't behave for me. Setting the column in QBE to count I then set criteria to the text value I want and then tells me I have a 'data type mismatch in criteria expression'.
I swiftly moved on to the second suggestion you made which has done the trick nicely.
Cheers
Tim
RE: Counting Discreet Text Values In a Report Q For Anthony
Excellent! Well done for persevering Tim!
Anthony
