Update Table Column with Sum of Two Numbers in Two Tables

Question

Update Table Column with Sum of Two Numbers in Two Tables

Keith A 40

Relationship

I have ran into a brick wall trying to create an update query. I want to update the number stored in the column of a table with the sum of another number in the same table and another number in another table. Attached is the relationship between the tables. The ServiceLog table has thousands of rows and the Services table has a few rows. What I want to do is create a button on a form that updates the LNextDue value in the ServiceLog table with the sum of LMileage and SMiles where the the record's LServiceID equals the ID of Services. I've tried a dozen different ways with no results or with the error "Operation must use an updateable query". Any help would be greatly appreciated.

Answer accepted by question author

2 additional answers

Your answer

Answer 1

Ken Sheridan 3,546

I think the problem you have encountered is due to your confusing summation and addition. In this case it is the latter you want.

Normally you would not store the mileage when a service is next due. It can be computed easily in a query. To store the value in a column in the ServiceLog table introduces redundancy, and the consequent risk of update anomalies. Having dropped the LNextDue column from the table, the query would then be:

SELECT
    ServiceLog.*,
    LMileage + SMiles AS LNextDue
FROM
    ServiceLog INNER JOIN Services
    ON ServiceLog.LServiceID = Services.ID
ORDER BY
    LDate;

In your case you would only store the mileage when a service is next due if you wish to maintain the values in previous rows on the basis of the original service interval, while computing subsequent values on the basis of the amended service interval. In that context Viorel has given you the correct solution.

I notice that the relationship between the tables is not enforced. An unenforced relationship serves no useful purpose. To enforce the relationship select the Enforce Referential Integrity option in the Edit Relationships dialogue.

Keith A 40 Reputation points

2025-11-10T14:32:50.6466667+00:00

You are exactly right. This and Viorel's solution both work. I am going to drop the column and calculate on the fly as you have suggested as it is easier in the long run. Thank you all for your help!
Ken Sheridan 3,546 Reputation points

2025-11-11T14:30:22.17+00:00
Bear in mind that doing that will mean that if you change the service interval, the LNextDue values returned by the query will change for all previous ServiceLog records, as well as for new records. If you want to preserve the original values from the time of those services, then you'll need to assign the new value to a LNextDue column when a row is inserted into the ServiceLog table. The place to do this would be in a data entry form, in which code would be executed in the AfterUpdate event procedures of the ServiceID and Mileage controls. There are various ways you can do this, e.g.

Dim strCriteria As String strCriteria = "ServiceID = " & Me.ServiceID If Not IsNull(Me.ServiceID + Me.Mileage) Then Me.LNextDue = Me.Mileage + DLookup("SMiles", "Services", strCriteria) End If

Answer 2

Viorel 125.6K

Try this query too:

UPDATE ServiceLog
INNER JOIN Services ON ServiceLog.LServiceID = Services.ID
SET
    ServiceLog.LNextDue = [ServiceLog].[LMileage] + [Services].[SMiles];

Answer 3

To create an update query that updates the LNextDue value in the ServiceLog table with the sum of LMileage from the ServiceLog table and SMiles from the Services table based on the matching LServiceID, you can use the following SQL syntax:

UPDATE ServiceLog
SET LNextDue = LMileage + (SELECT SMiles FROM Services WHERE Services.ID = ServiceLog.LServiceID)
WHERE LServiceID IN (SELECT ID FROM Services);

Explanation:

The UPDATE statement targets the ServiceLog table.
The SET clause specifies that LNextDue should be updated to the sum of LMileage and SMiles from the Services table.
The subquery (SELECT SMiles FROM Services WHERE Services.ID = ServiceLog.LServiceID) retrieves the SMiles value corresponding to the LServiceID in the ServiceLog table.
The WHERE clause ensures that only records in ServiceLog that have a corresponding ID in the Services table are updated.

Make sure to test this query in a safe environment before applying it to your production database, as updates cannot be undone without a backup.

Answer 4

Duane Hookom 26,545 Volunteer Moderator

Typically, you don't want to store values that can be computed/calculated on-the-fly. Your question didn't mention either the date or vehicle fields in your log table. I expect one or both of these are significant.

I anticipate the use of DSum() could solve your problem since any aggregate query will render your table read-only.

Keith A 40 Reputation points

2025-11-07T18:45:31.8+00:00

This will be a VERY rarely used function and the number being stored will only be calculated once. It's for a fleet management database. They are wanting the ability to change the SMiles value for a vehicle service (like an oil change). The Services table is very small and stores this schedule. They are wanting to change the mileage from 5000 to 5500 and I am looking for a way to add this new mileage above to the LMileage field and store this sum in the LNextDue field. If worse comes to worse, I will export the tables, make the change in Excel, and reimport the tables. Thanks for your response.
Duane Hookom 26,545 Reputation points Volunteer Moderator

2025-11-07T20:30:43.3666667+00:00

Keith, you didn’t answer my question about the Vehicle and date. I would expect to see a vehicle table where the sum would be stored rather than the log table.

Share via

Update Table Column with Sum of Two Numbers in Two Tables

2 additional answers

Explanation:

Your answer