Record Count

[Anthony W]

I have two tables, one gives information on Students and
the other gives information on training schemes they have
been on. It is a one-to-many relationship between the two
tables. Is it possible to have a field in a table which
will count how many training schemes one student has been
on and if so how please?

Thanks in Advance


Anthony Webb

 

[tina]

i wouldn't save that info in a table field, because it can
change. if one student record in the parent table (one)
can have many training scheme records in the child record
(many), then you can count the total schemes per student
at any time you need it, in a query, form, or report - by
using calculated fields or controls.

 

[Jeff Boyce]

Anthony

As Tina pointed out, you'd have to create a way to update that "total" every
time anything changed on the Training Schemes table.

Instead, why not just do a totals query against the Schemes, grouped by
student? If you've designed your Schemes table to include a foreign key
that points back to the Students table, so you can tell which student a
training scheme belongs to, you can group on that foreign key.

Any time you'd need to "lookup" the count of Schemes for a particular
Student, you could use a parameter in your totals query to limit it to
counting that students schemes.

Check Access HELP for "Totals query".

Good luck

Jeff Boyce
<Access MVP>