Update Query using Multiple Source Tables

[Allan]

Is it possible to use an update query to change the value
of a field based on inputs from several other tables?

I have created a budget database, w/ the main table being
cost center data. I want to update the payroll account,
using an update query, dependent on 3 other tables:
Payroll ("BasePay" Field)
WeekCount (ie: Decemeber)
Increases (ie: December)

The field within the main "CostCenter" table would be a
function of these tables as such:
[CostCenter]![December] = [Payroll]![BasePay]/52 *
[WeekCount]![December] * [Increases]![December] where
[CostCenter]![Account] = "123456"

There happens to be a many to one relationship between
the "Payroll" table and the "CostCenter" table where the
link is cost center (many employees belong to a single
cost center). Each of the tables have established links.
The data would also need to be aggregated before the
update took place.

[Michel Walsh]

Hi,

It is surely possible to update a table with data from other tables. You will have to restrict the
records of the other tables through appropriate WHERE clause, such as

WHERE otherTable.DateTime= (SELECT MAX(otherTable.DateTime) FROM otherTable WHERE
otherTable.productID = updatedTable.ProductID)

By the way, use a dot, not a bang, between the table name and the field name.

Hoping it may help,
Vanderghast, Access MVP

"Allan" <AKoo...@etownwater.com> wrote in message news:081601c33746$f2bee120$a001...@phx.gbl...