471,573 Members | 1,825 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,573 software developers and data experts.

Update query question

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
May 22 '06 #1
1 1405
"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.
May 22 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

17 posts views Thread by kalamos | last post: by
2 posts views Thread by Mike Leahy | last post: by
10 posts views Thread by Randy Harris | last post: by
12 posts views Thread by si_owen | last post: by
reply views Thread by lumer26 | last post: by
reply views Thread by lumer26 | last post: by
reply views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.