I have a table called Member. In this table is a field called NoOfLessonsBooked. When a Member has booked a total of 10 lessons, they should get a free lesson. I have another table called Booking, which stores details of all the bookings that have been made. Every booking includes a MemberId for the member who made the booking. I want the NoOfLessonsField to go up by 1 for every record in booking that includes the MemberID of a member. So every time MemberID 1 makes a booking, I would like MemberID 1's record in the member table to update it's NoOfLessonsBooked by 1.
Here are the 2 tables in question:
Member Table
MemberID | MemberForename | MemberSurname | MemberDOB | MemberAddress | MemberTown | MamberPostCode | MemberTelNo | MemberEmail | MemberMedCert | NoOfLessonsBooked |
---|---|---|---|---|---|---|---|---|---|---|
1 | Reece | Agnew | 29-Oct-06 | SomeWhere 1 | SomeTown | ZX81 5PK | 0101 323656 | ReeceA@email.com | No | 1 |
2 | John | Hanna | 23-Dec-74 | SomeWhere 2 | SomeTown | ZX80 3CL | 102 323656 | JohnH@email.com | No | 1 |
3 | Arthur | Marston | 13-Nov-58 | SomeWhere 3 | SomeTown | ZY1 2BC | 103 323656 | ArthurM@email.com | Yes | 0 |
4 | Bradley | Stone | 09-Aug-01 | SomeWhere 4 | SomeTown | ZX81 5PK | 104 323656 | BradleyS@email.com | No | 0 |
5 | Dawn | Pearson | 08-Mar-02 | SomeWhere 5 | SomeTown | ZX80 3CL | 105 323656 | DawnP@email.com | No | 0 |
6 | Freya | Adams | 27-Feb-03 | SomeWhere 6 | SomeTown | ZY1 2BC | 106 323656 | FreyaA@email.com | No | 1 |
7 | Aurora | Bell | 24-May-12 | SomeWhere 7 | SomeTown | ZX81 5PK | 107 323656 | AuroraB@email.com | No | 2 |
8 | Mike | Graham | 12-Dec-56 | SomeWhere 8 | SomeTown | ZX80 3CL | 108 323656 | MikeG@email.com | Yes | 0 |
9 | Jean | Marston | 17-Aug-60 | SomeWhere 9 | SomeTown | ZY1 2BC | 109 323656 | JeanM@email.com | No | 1 |
10 | Iris | Chambers | 18-Oct-06 | SomeWhere 10 | SomeTown | ZX80 3CL | 110 323656 | IrisC@email.com | No | 0 |
Bookings Table
BookingID | BookingDate | BookingTime | MemberID | ActivityID | GroupID |
---|---|---|---|---|---|
1 | 03-Oct-24 | 10:00 | 2 | 1 | |
2 | 07-Oct-24 | 11:00 | 5 | 2 | |
3 | 21-Oct-24 | 09:00 | 1 | 2 | |
4 | 29-Oct-24 | 13:00 | 4 | 7 | |
5 | 05-Nov-24 | 12:00 | 3 | 3 | |
6 | 13-Nov-24 | 11:00 | 9 | 1 | |
7 | 17-Nov-24 | 10:00 | 4 | 2 | |
8 | 20-Nov-24 | 15:00 | 2 | 6 | |
9 | 15-Dec-24 | 16:00 | 6 | 1 | |
10 | 22-Dec-24 | 13:00 | 5 | 1 | |
11 | 09-Oct-24 | 12:21 | 7 | 1 | |
12 | 26-Oct-24 | 13:00 | 7 | 2 |
You could use an After Insert Data Macro on the booking table that updates the Member table and increments the NoOfLessonsBooked. You might also consider an Delete trigger on the booking table to subtract 1 from Member.NoOfLessonsBooked.
To create a Data macro that captures the After Insert event, use the following steps.
Open the table for which you want to capture the After Insert event. [Bookings]
On the Table tab, in the After Events group, click After Insert.
An empty data macro is displayed in the macro designer. Add the following Action blocks:
Look up A Record in Member
Where Condition = [MemberID] = [Bookings].[MemberID]
EditRecord
SetField
name [Member].[NoOfLessonsBooked]
value = [Member].[NoOfLessonsBooked] + 1
End EditRecord
see this question for more details