Access update query problem...

[helraizer]

Hi folks,

 

Didn't know which forum this'd go in, sorry if this is the wrong forum for it.

 

I am making a database about a theme park, with 4 tables: customers, reservation, rides and slots. Each ride costs a certain amount of credit, when the customer makes a reservation for a particular ride there 'credit' goes down by how much the ride is worth.

 

I am using an update query to do this (with DoCmd.RunSQL) on the form for reservations.

 

The SQL I'm using is:

 

UPDATE Members INNER JOIN (Rides INNER JOIN Reservation ON Rides.[Ride ID]=Reservation.[Ride ID]) ON Members.CustomerID=Reservation.[Customer ID] SET Members.[Account Balance] = Members.[Account Balance]-Rides.[Point value] WHERE Rides.[Ride ID] = Reservation.[Ride ID];

 

 

This works to an extent, but what I want/need is for it to only update it once at the time of update, for that one ride they book. At the moment it always updates every member for every ride that have booked.. So they keep getting 'charged' for the rides each time someone else books a ride..

 

How would I do this?

 

Hope that makes sense,

Sam