By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,323 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

update query problem.. (sort of)

100+
P: 118
Hi folks,

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:

Expand|Select|Wrap|Line Numbers
  1. 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];
  2.  
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
Jan 7 '08 #1
Share this Question
Share on Google+
3 Replies


JKing
Expert 100+
P: 1,206
Hi folks,

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:

Expand|Select|Wrap|Line Numbers
  1. 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];
  2.  
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
Hi Sam,

Are you displaying the ride price on the form as well? This would make updating easy. With your current SQL statement you are updating all related records. You need to specify the exact customer whose balance you want to update.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim strSQL As String
  3.  
  4. strSQL = "UPDATE Members SET Members.[Account Balance] = Members.[Account Balance] - " & Me.RideCost & " WHERE Members.CustomerID = '" & Me.CustomerID & "'"
  5.  
  6. DoCmd.RunSQL strSQL
  7.  
Me.RideCost would be the control you are using to display the ride cost
Me.CustomerID would be the control you are using to hold the customerID
Jan 7 '08 #2

100+
P: 118
Hi Sam,

Are you displaying the ride price on the form as well? This would make updating easy. With your current SQL statement you are updating all related records. You need to specify the exact customer whose balance you want to update.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim strSQL As String
  3.  
  4. strSQL = "UPDATE Members SET Members.[Account Balance] = Members.[Account Balance] - " & Me.RideCost & " WHERE Members.CustomerID = '" & Me.CustomerID & "'"
  5.  
  6. DoCmd.RunSQL strSQL
  7.  
Me.RideCost would be the control you are using to display the ride cost
Me.CustomerID would be the control you are using to hold the customerID
Hey, thanks for that.

Although, I get "Run-time Error '3464: Data type mismatch in criteria expression" - I can't understand why though.. The field types match. Any ideas?

Thanks,
Sam
Jan 8 '08 #3

jaxjagfan
Expert 100+
P: 254
Hey, thanks for that.

Although, I get "Run-time Error '3464: Data type mismatch in criteria expression" - I can't understand why though.. The field types match. Any ideas?

Thanks,
Sam
strSQL = "UPDATE Members SET Members.[Account Balance] = Members.[Account Balance] - " & Me.RideCost & " WHERE Members.CustomerID = " & Me.CustomerID

Change the SQL to this if CustomerID is Long or Numeric datatype.
Jan 8 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.