Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Sep 2004
    Location
    Wolverhampton, Staffordshire, England
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting incidents associated with a pupil (2000)

    Hello there, i am having some more trouble with access, i cannot figure out how to get the system to count how many incidents are associated to each pupil, currently it is manually done, but this is not a good idea... Is there any code or help you could give me to try and get it to count how many incidents there are to each pupil? Many thanks,
    Danny D

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

    Re: Counting incidents associated with a pupil (2000)

    Assuming that you have a table in which each record corresponds to an incident, you can do the following:
    - Create a query in design view, based on the table.
    - Add the field or fields identifying the pupil (probably some kind of pupil number, plus name if you want)
    - Select View | Totals or click the Totals button on the toolbar.
    - Add a calculated column <code>NumberOfIncidents: Count(*)</code>
    - Set the Total option for this column to Expression (it should be Group By for the other columns).
    - Switch to datasheet view to see the result.

  3. #3
    Star Lounger
    Join Date
    Sep 2004
    Location
    Wolverhampton, Staffordshire, England
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting incidents associated with a pupil (2000)

    that is perfect, how can i now get it to show at the bottom of the pupil form? sorry i get lost in all the ins and outs, i tried setting the control source and it says #Name?

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

    Re: Counting incidents associated with a pupil (2000)

    You could create a form bound to the Totals query, and place it as a subform in the footer of the pupils form. The subform should be linked to the main form on the field that identifies the pupil, through the Link Child Fields and Link Master Field properties of the subform. Access usually takes care of this automatically.

    Alternatively, you can put a text box in the footer of the pupils form with control source
    <code>
    =DCount("*","qryTotal","PupilID = " & [PupilID])
    </code>
    where qryTotal is the name of the totals query and PupilID is the field that identifies the pupil. In the above expression, it is assumed to be a number field, if it is a text field, use the following instead:
    <code>
    =DCount("*","qryTotal","PupilID = '" & [PupilID] & "'")</code>

  5. #5
    Star Lounger
    Join Date
    Sep 2004
    Location
    Wolverhampton, Staffordshire, England
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting incidents associated with a pupil (2000)

    i tried the subform but it come as a datasheet view so decided to try the textbox method, it works but it shows 1 incidents for all pupils, it ppear to update on record change but still says 1, any suggestions?

  6. #6
    Star Lounger
    Join Date
    Sep 2004
    Location
    Wolverhampton, Staffordshire, England
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting incidents associated with a pupil (2000)

    Nevermind hehe, managed to figure it out using subforms, just needed to change view, sorry for being so simple Hans and your help has as usual been invaluable, i give you my thanks,
    Danny D

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

    Re: Counting incidents associated with a pupil (2000)

    You can specify in design view whether a form will be single, continuous or datasheet form.

    And I

Posting Permissions

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