Update Statments

ramdil · #1 July 3rd, 2008, 04:01 PM

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

Expand|Select|Wrap|Line Numbers

  
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 · #2 July 3rd, 2008, 06:11 PM

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.

Expand|Select|Wrap|Line Numbers

 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 · #3 July 4th, 2008, 04:01 PM

Hi

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

Quote:

Originally Posted by 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.

Expand|Select|Wrap|Line Numbers

 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

Update Statments

re: Update Statments

re: Update Statments

/bytes/development

/bytes/it

/bytes/about