473,320 Members | 1,845 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

update query problem.. (sort of)

118 100+
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
3 2012
JKing
1,206 Expert 1GB
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
helraizer1
118 100+
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
254 Expert 100+
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

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

Similar topics

4
by: Karaoke Prince | last post by:
Hi There, I have an update statement to update a field of a table (~15,000,000 records). It took me around 3 hours to finish 2 weeks ago. After that no one touched the server and no...
2
by: Mike Leahy | last post by:
Hello all, This question is related to updating tables - is there any way to calculate or update the values in a column in a table to the values in a field produced by a query result? An...
2
by: Patrick Drouin | last post by:
Hello everyone, I'm quite new to ACCESS and I have a fairly good knowledge of SQL under Oracle. I'm trying to run the following query without any luck: UPDATE M1L SET M1L.Code_err = (SELECT...
10
by: Randy Harris | last post by:
I imported records into a table, later found out that many of them had trailing spaces in one of the fields. If I'd caught it sooner, I could have trimmed the spaces before the import. This...
20
by: Development - multi.art.studio | last post by:
Hello everyone, i just upgraded my old postgres-database from version 7.1 to 7.4.2. i dumped out my 7.1 database (with pg_dump from 7.1) as an sql-file with copy-commands and to one file using...
3
by: turtle | last post by:
I have Two tables (Table1 and Table2). Both tables have a common field called part number. Table 1 contains an extra field that i would like to update table 2 to match if the part number matches....
1
by: bughunter | last post by:
simple query select * from "Result" res where (res."QID" = 51541 or res."QID" = 51542) works fine ("SRV-BL"."Result" ~ 900000 rows) and returns 36 rows but update - no! update...
0
by: David Linsin | last post by:
I created a simple test case to reproduce the problem (also check Bug #15500): import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement;...
16
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...
3
by: Michel Esber | last post by:
Hi all, DB2 V8 LUW FP 15 There is a table T (ID varchar (24), ABC timestamp). ID is PK. Our application needs to frequently update T with a new value for ABC. update T set ABC=? where ID...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.