Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    count function a report (2000)

    Hello, I have bulit a report to show the names of people who refer queries to a given department. I need to be able to have a field on the report that counts entries for each person. I am okay with e.g. count ([queries]) but it's not the number of queries as whole I need, it is the number of queries per individual. How would I code this?

    Thank you.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: count function a report (2000)

    The easiest way is to group the report by person and to add a group header or footer (use the Sorting and Grouping window for this). A text box with a Sum or Count in its control source will sum or count only the records in the group.

    If you can't or won't use grouping, you can use DCount in the control source of the text box. Let's say that the individuals are identified by a numeric field PersonID, and that your report os based on a table tblData. The control source would look like<pre>=DCount("*", "[tblData]", "[PersonID]=" & [PersonID])</pre>

    If PersonID is a text field, you need to add quotes around it:<pre>=DCount("*", "[tblData]", "[PersonID]='" & [PersonID] & "'")</pre>


  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count function a report (2000)

    Thanks again Hans, you are helping me develop better than any book.

  4. #4
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count function a report (2000)

    Does this work in the header as well? I tried 'count' but it returned 1's, whereas in the footer it returned the correct number.

    Thanks,

    Kiwi44

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: count function a report (2000)

    I haven't seen that behavior, but it probably depends on the way your report is organized. Can you provide some details?

  6. #6
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count function a report (2000)

    I have a report based on a query (qryList), which contains MainID, Section, SubSection, Title. In the report, I group on Section and SubSection. What I want to do is put the number of 'Title's in each Section and Subsection in parentheses after the Section or Subsection title (i.e. Section (count)).

    Based on your comments, I realised that DCount was probably not appropriate, and found that Count gives me the right answer. However, I now have another problem: I can show the result of Count(MainID) in an unbound text box on its own, but when I try =[Section] & " (" & Count(MainID) & ")", I get '#Error'.

    Can I do what I want to do in the Control Source property? If I enclose the [Section] in quotes, the error goes away, but of course it just reads '[Section] (5)', etc.

    Thanks for insight into the trick here!

    Kiwi44

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: count function a report (2000)

    Access probably gets confused because Section is also a property of the report. Does it work for SubSection?

  8. #8
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count function a report (2000)

    That did the trick! Another useful lesson learned!

    Thanks very much!

    Kiwi44

Posting Permissions

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