Counting Discreet Text Values In a Report Q For Anthony

Resolved · High Priority · Version 2007

Thu 16 Feb 2012, 16:48 Delegate 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:21 Trainer 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:46 Delegate 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:16 Trainer Anthony said...

RE: Counting Discreet Text Values In a Report Q For Anthony

Excellent! Well done for persevering Tim!

Anthony

Training courses

	98.8 % recommend us
	More live stats...

Training information:

Access tip:

Choosing data types in Access

Not 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.

- The Text data type can accept up to 255 characters. For information that will be recorded in paragraphs, use Memo.

- Also use Text for numbers that aren't going to be used in calculations, e.g. phone numbers.

- Use the Currency data type for monetary amounts.

- Use Date/Time for dates.

- Most other numbers can use the Number data type, but the Field Size property may have to be altered.

- For fields that have only two alternatives (yes, no) use Yes/No data type. If there is likely to be a third entry option (e.g. maybe or don't know) use Text instead.

View all Access hints and tips