Say I have 3 tables:

tblStudent
Student ID (pk), Name, No. of performances

tblPerformance
Performance ID (pk), Details, Students required, Date

tblBookings
Performance ID, Student ID (Duplicate key)

Because a single performance could have any number of students, tblBookings would have repeating performance IDs and repeating student IDs e.g.

Performance ID Student ID
1 1
1 2
1 3
2 1
2 4


What I want to be able to do is update the No. of performances field for each student in tblStudent by counting the number of related Student ID occurences in tblBookings.

I think I need to run a macro when the table is opened to update fields, but I don't know how to go about creating the query / macro for the actual count function.

Any help much appreciated.