I could use some advice - I suspect this is an EASY query - just cant get it straight.

[MGraham]

Ok - setup. 3 tables as below with obvious links. Reservations, Tickets,
Events

I have a Reservations form with subform Tickets all working OK.
What I need to be able to do is to add new tickets to the subform and by
doing so trigger a query to update SoldTickets in Events table with a count
of all the tickets for that event so when one is sold, it is reflected
immediately in the Events table.

I'm relatively good with SQL Queries from (gasp!) MySQL and PHP. But now
I'm trying to do something in Access and it is befuddling me. Essentially
what I want to do is:

A.) Pick the right trigger event for the subform so it only triggers when a
new record is added OR a record is deleted in Tickets.
B.) Get a count of all tickets with EventID equal to the EventID in the main
Reservations form and use that to...
C.) UPDATE Events SET SoldTickets = (count of all tickets from B.) WHERE
EventID = (EventID of Reservations form)

Any help with this would be greatly appreciated. I feel like it CANT be
that difficult but I've been struggling with it for hours. I've tried
queries, queries in queries, macros, VBA events... Help!

Tickets
- TicketID
- ReservationID
- EventID

Events
- EventID
- SoldTickets

Reservations
- ReservationID

 

[[MVP] S.Clark]

Form's AfterUpdate and AfterDeleteConfirm events