Question How to add Count field

[DaveG]

Hi There,

Currently using 2003 and have been altering the Classroom management database found here: (Oops can't post links) Okay its the standard Access 2003 template on their website

On that page you can also see the form I'm currently working on and where my question relates to. You'll notice five students booked onto that course. The students are added to the subform via DISTINCTROW

SELECT DISTINCTROW [Students And Classes].StudentID, Students.Trust, [Students And Classes].Attendance, Students.JobTitle, [Students And Classes].ClassID FROM Students INNER JOIN [Students And Classes] ON Students.StudentID=[Students And Classes].StudentID;

The Question: I'd like to have a field on the main form (called Booked) that counts the number of students booked on the course within the subform (counting Student) where another given field I've added (attendance) is null. (or even better is not one of three possibilities, as there are four options for that field - null, Attended, Cancelled and DNA. (so not count Cancelled but count the rest)

Many thanks if anyone can assist, and FYI, I'm okay with most of access bar VBA and building code. (I know need to learn more)

Cheers
Dave

PS - For Mods, posted in General as unsure where exactly this question should go!

 

[Jeanette Cunningham]

To get the count, you create a totals query: In the query designer, right click in the grid and choose Totals. For the column that is going to show the count, choose 'count' instead of group by. Note, you usually need to put 2 copies of the field you want to count on - one column for that field will have 'group by' in the totals row, the second column will have 'count'.
Here is a link about how to create different types of queries:
http://www.fmsinc.com/tpapers/queries/

 

[DaveG]

Jeanette, thanks for the info and that link, very informative. My problems seems to be taking the count field from within Districtrow within the subform source and putting it into the main form.
If this was a simple form and subform think it would have been easier as I've done it before, but because the subform is create from the distinctrow code and the name is create from StudentID its giving me headaches, although enjoyable puzzle (for now)

 

[Jeanette Cunningham]

The same technique works. Don’t let the DistinctRow get in the way of understanding how to create the query you need. You must set up the totals query with enough fields to identify which rows you want.

If you just want to know how many records on the subform after the filter is applied, you can do:
Me.TextBoxName = Nz(Me.[SubformControlName].Form.RecordsetClone.RecordCount,0)

Note: replace my object names with yours.,

 

[DaveG]

Cheers Jeanette, think you were right, I was letting DistinctRow get in my was. All sorted now.

Thanks again
Dave