# Thread: Counting in Reprt (2000)

1. ## Counting in Reprt (2000)

I hope I can explain what I am doing. I am using a totals query that is grouped by district, then by Client Number.
I am counting fields A, B, C, D,E, F, G, H which have A's, B's etc as their data. The A,B,C etc fields could have a number like 1, 20, 23 etc Each Client number could have various totals like A -4, B - 30 etc. I took that totals query and put it in another query and in a column next to each of the Countof A, Countof B etc I put a calculation like this: A: IIf([CountOf A]=0," ","A") so that it replaces the count number whatever it may be to a letter. Now I use that query in a report. The report is broken down by District then it has Clients. Now I want a count of all the As, Bs, Cs per district . If there are 10 clients for a district (for example) instead of counting the As, Bs, Cs etc it is giving me a count of the Clients which is 10 for field A, B, C. etc. Field A could have 3 A's but it still counts it by number of clients which is 10. I want a count of 3. How can I get it to count the A's, Bs,Cs etc instead of the Clients. I saved it as Excel thinking I could use CountA in each of the columns but it does the same thing. Even though there are blanks, it counts the number of clients. Help!!

2. ## Re: Counting in Reprt (2000)

Use something like

=Abs(Sum([A]="A"))

or

=Abs(Sum([CountOfA]>0))

Explanation: the expressions [A]="A" and [CountOfA]>0 both evaluate to True or False. In fact, these are stored in memory and in tables as -1 and 0. So, if a column has 3 "A"s, the expression will be -1 in 3 cases, and 0 in the rest. By summing this, we get -3. The Abs function removes the minus sign, and we are left with 3, i.e. the number of "A"s.

3. ## Re: Counting in Reprt (2000)

That worked perfectly in Access but unfortunately I have to save that report to Excel. When I save it to Excel that calculation doesn't show up in Excel. Is there a calculation in Excel that uses Abs because if I have to I will count it manually in Excel if the report cannot not be saved to Excel with this calculation.

4. ## Re: Counting in Report (2000)

The Excel ABS worksheet function works the same as VBA Abs() function in Access. It will return the absolute value of a number. Ex: cell A1 has value of -1. The formula =ABS(A1) will return the value 1. When exporting data to Excel, in general, if a function (such as Abs()) returns a numerical value, the data in the spreadsheet will be represented as a number. I usually only export to Excel from either a query or a recordset opened in code, not directly from a report, so I don't know why the calculation isn't being translated in this case.

HTH

#### Posting Permissions

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