Update Statments

[ramdil]

Hi All
Please help me .Hope i have described my problem clearly below.
I have a condition where i need to update some data in table from the value taken from another table.So basically i need to create a update statment to perform the data manipulation.but i am not able to write the update statments.I have two tables..Tab_Bookings and tab_Booking_Fare_Lines .Both are joined by field Booking_ID.Now there is a column No_Passengers in Tab_Booking and there is a column called Number_of_Passengers in Tab_Booking_child.Now i want a update statment which will update No_of_Pass_Type_1 column in Tab_Booking table from
tab_Booking_Fare_Lines for each booking_ID.Thanks in advance.The main problem for me here is that data is in one to many data.so i need to take the Sum([tab_Booking_Fare_Lines].[Number_of_Passengers])
I have written a update statements but its creating an error for me which says "You tried to execute a query that doesnot inclue expression No_of_Pass_Type_1 as part of aggregate function.

Thanks in advance

 
UPDATE tab_Bookings INNER JOIN tab_Booking_Fare_Lines ON tab_Bookings.File_No = tab_Booking_Fare_Lines.Booking_Id SET tab_Bookings.No_of_Pass_Type_1 = Sum([tab_Booking_Fare_Lines].[Number_of_Passengers])
 
 

[ADezii]

Your description of the problem is a little confusing and contradictory, but it can be solved in the following manner. I'm sure there is a SQL answer, but right now it elludes me.

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim lngTotalPassengers As Long
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("Tab_Bookings", dbOpenDynaset)
 
Do While Not MyRS.EOF
  lngTotalPassengers = Nz(DSum("Number_Of_Passengers", "Tab_Booking_Fare_Lines", _
                               "[Booking_ID] = " & MyRS![Booking_ID]), 0)
    MyRS.Edit
      MyRS![No_Passengers] = lngTotalPassengers
    MyRS.Update
  MyRS.MoveNext
Loop
 
MyRS.Close
Set MyRS = Nothing

[ramdil]

Hi

Thanks for VBA code.If you find sql query and if you have time then do please post it.

Your description of the problem is a little confusing and contradictory, but it can be solved in the following manner. I'm sure there is a SQL answer, but right now it elludes me.

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim lngTotalPassengers As Long
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("Tab_Bookings", dbOpenDynaset)
 
Do While Not MyRS.EOF
  lngTotalPassengers = Nz(DSum("Number_Of_Passengers", "Tab_Booking_Fare_Lines", _
                               "[Booking_ID] = " & MyRS![Booking_ID]), 0)
    MyRS.Edit
      MyRS![No_Passengers] = lngTotalPassengers
    MyRS.Update
  MyRS.MoveNext
Loop
 
MyRS.Close
Set MyRS = Nothing