Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Counting (Access 2000)

    I have a form that has a text box in which I want to count the number of persons in a subform. Each person can have multiple requests; however, I only want to count each person once. The subform has 4 fields
    Easy Access

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting (Access 2000)

    If you added a hidden subform, or reduced its size to 0,0,0,0. (tab order = no).
    You could add a totals query based on name only, then set the "Total" Field to Group By.
    This would then only show each name once and could be referred to the main form from there.

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

    Re: Counting (Access 2000)

    Assumptions:
    - Main form and subform are linked by a field called ID.
    - Main form is named frmMain.
    - The record source of the subform is a table or query named srcData (this won't work if the record source is an SQL statement)

    1. Create a query to return the unique persons. Its SQL is

    SELECT DISTINCT [PersonID]
    FROM [srcData]
    WHERE [ID]=[Forms]![frmMain]![ID]

    Save this query as qryDistinctPersons.

    2. Put a text box on the main form with control source

    =DCount("*";"[qryDistinctPersons]")

    In my test, this text box updated automatically when I moved from record to record in the main form. It didn't when the data in the subform were modified; you would have to write code to update the text box whenever the data in the subform change.

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Counting (Access 2000)

    Thank you so much for your help....worked like a charm.
    Easy Access

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Counting (Access 2000)

    Thank you for the suggestion. I tried both and I liked Hans' a little better.
    Easy Access

Posting Permissions

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