98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Access Training and help » Counting Discreet Text Values In a Report Q For Anthony
Counting Discreet Text Values In a Report Q For Anthony
Resolved · High Priority · Version 2007
Thu 16 Feb 2012, 16:48Delegate Timothy said...
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
Fri 17 Feb 2012, 09:21Trainer Anthony said...
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
Fri 17 Feb 2012, 13:46Delegate Timothy said...
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
Fri 17 Feb 2012, 14:16Trainer Anthony said...
RE: Counting Discreet Text Values In a Report Q For Anthony
Excellent! Well done for persevering Tim!
Anthony
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Access tip:Choosing data types in AccessNot sure which data type to use for your Access fields? Here are some guidelines to help you choose a data type to assign to a field. |
