Update queries - calculated values based off each other..?

[snipe]

Hey guys,



Wasn't sure how to title this. I have a main table of payroll data. A user can specify an employee and a date range and click "edit." This moves appends that bit of data to secondary table (after deleting previous entries). In this secondary table, the user can change hours, commission, etc. Once they are happy with their changes they click "recalculate." This will run an update query on a bunch of other fields in the secondary table. "HourlyWages", "SalesCommission", and "TotalPay" are the ones that I am concerned with right now. Our employees get paid a base hourly wage plus their commission.

My question:

Can a single update query recalculate HourlyWages and SalesCommission AND use those 2 new values to calculate TotalPay?

There are 25 fields that will actually be updated, but those three give the general idea of my question. I don't want to program all of this to have it not work.

[snipe]

Ya, doesn't work. Went the VBA route.

[Dal Jeanis]

I wouldn't take a bet upon the ORDER in which fields would be updated, so if you are trying to update a field that will be the source of calculation for the update of a different field, then I wouldn't recommend it.

For this kind of thing, I always use a temporary table as an intermediate step, which both avoids any ambiguity and simultaneously also avoids several kinds of syntax errors and Jet error messages.

VBA works fine, too - although for large tables, a maketable query followed by an update query would tend to be faster.