Search code examples
sqlvbams-access

Updating a table with the value from another table. Date Dependent

I need help figuring out a way to update a value in one table with the value of another in a different table where that different values' date is the most recent (or Max Value). Currently this is what I have:

UPDATE tblVehicles INNER JOIN tblVehicleDailyDriveDetails ON [tblVehicles].[VehicleID] = [tblVehicleDailyDriveDetails].[VehicleID] SET [tblVehicles].[ODO] = [tblVehicles][.ODO] + [tblVehicleDailyDriveDetails].[TRIP] WHERE [tblVehicleDailyDriveDetails].[DateDriven] = Max([tblVehicleDailyDriveDetails].[DateDriven])

The result of this that I've been getting has been that I can't use an aggregate function in a WHERE clause.

I've even tried to use a query to update the table, by filtering the other table using the Max function; however the result of that was an error message indicating the operation must be an updateable query.

Does anyone have any ideas on how to make such a function work? Basically the user will be inputting the miles driven each day into the database via a data entry form. After submitting that days total driven miles, it will add onto the vehicles total milage driven since in service.

Solution

  • Use a subquery:

    UPDATE 
    tblVehicles 
INNER JOIN 
    tblVehicleDailyDriveDetails 
    ON [tblVehicles].[VehicleID] = [tblVehicleDailyDriveDetails].[VehicleID] 
SET 
    [tblVehicles].[ODO] = [tblVehicles][.ODO] + [tblVehicleDailyDriveDetails].[TRIP] 
WHERE 
    [tblVehicleDailyDriveDetails].[DateDriven] = 
    (Select Max([DateDriven]) 
    From tblVehicleDailyDriveDetails)