Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    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!!

  2. #2
    Plutonium Lounger
    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.

  3. #3
    5 Star Lounger
    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.

  4. #4
    Bronze Lounger
    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

Posting Permissions

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