Update query question

[G Gerard]

Hello

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?

Thanks

G.Gerard

[Bob Quintal]

"G Gerard" <gg*****@nbnet.nb.ca> wrote in
news:mt********************@ursa-nb00s0.nbnet.nb.ca:

Hello

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?

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.