Update query question

Update query question

Post by G Gerar » Tue, 23 May 2006 10:28:50


I am trying to update a table (TableB) using records

from a second table (TableA)

Both TableA and TableB have a Field1 and a Field2; Field1 in both tables

have matched values so I have Inner Join Field1 of both tables in an

Update query so that the correct records in TableB are updated with

the matching records from TableA (on the Inner Join Field1).

Field2 is the field that is being updated.

The problem is that in TableA there are records that have duplicate

values in Field1 and therefore the query decides which one of the

duplicate records from TableA it will use to update TableB;

I would like to made that decision.

TableA also has a Field3 that is a number field and I would
like the query to choose the record with the largest value in
Field3 when duplicate values in Field1 are encountered
in TableA during the updating process.

Any ideas?



Update query question

Post by Bob Quinta » Tue, 23 May 2006 11:07:34

"G Gerard" < XXXX@XXXXX.COM > wrote in

Create and save3 an aggregate query from tableA that groups on
field1 and returns the max(field3)

Since Access will complain that the query is not updateable,
create a maketable query from this with an Inner join to tableA

Use the newly created table to update tableB.

-- Bob Quintal

PA is y I've altered my email address.