473,230 Members | 4,187 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,230 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 1474
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Surendra | last post by:
I have this query that I need to use in an Update statement to populate a field in the table by the value of Sq ---------------------------------------------------------------------------- Inline...
17
by: Felix | last post by:
Dear Sql Server experts: First off, I am no sql server expert :) A few months ago I put a database into a production environment. Recently, It was brought to my attention that a particular...
17
by: kalamos | last post by:
This statement fails update ded_temp a set a.balance = (select sum(b.ln_amt) from ded_temp b where a.cust_no = b.cust_no and a.ded_type_cd = b.ded_type_cd and a.chk_no = b.chk_no group by...
2
by: Mike Leahy | last post by:
Hello all, This question is related to updating tables - is there any way to calculate or update the values in a column in a table to the values in a field produced by a query result? An...
4
by: dp | last post by:
After looking and looking, it appears that Access ADPs graphic query designer won't display: update customer set = . from customer, where customer. = .; It comes up with the "Query...
10
by: Randy Harris | last post by:
I imported records into a table, later found out that many of them had trailing spaces in one of the fields. If I'd caught it sooner, I could have trimmed the spaces before the import. This...
5
by: Andrew | last post by:
I've got a list box that selects a record on a subform, and in that subform are a few text fiels and a button that runs an update query. How do I have the update query button run and only update...
4
by: deko | last post by:
I'm trying to update the address record of an existing record in my mdb with values from another existing record in the same table. In pseudo code it might look like this: UPDATE tblAddress SET...
12
by: si_owen | last post by:
Hi all, I have a SQL query that worked fine in my project until it came to testing. I found that the NvarChar fields I have wont accept the use of an ' My code and query is here does anyone...
16
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

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.