Results 1 to 4 of 4
Thread: Counting in Reprt (2000)

20031024, 15:24 #1
 Join Date
 Jan 2001
 Posts
 1,119
 Thanks
 0
 Thanked 0 Times in 0 Posts
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!!

20031024, 15:51 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.

20031024, 16:11 #3
 Join Date
 Jan 2001
 Posts
 1,119
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20031024, 18:08 #4
 Join Date
 Nov 2001
 Location
 Arlington, Virginia, USA
 Posts
 1,394
 Thanks
 0
 Thanked 3 Times in 3 Posts
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