Solved

Access Append and Update Query

Posted on 2011-03-01
13
946 Views
Last Modified: 2012-08-13
Hi there,

I have two sample tables attached tbl1 and tbl2. What I am trying to do is:

1. append and update the new [NumName] entries that appear in tbl1 into tbl2 with their [NumName] and [Size] respectively. I currently have the below query which isn't working
Exists (SELECT * FROM tbl1 WHERE tbl2.NumName=tbl1.NumName)

Open in new window


2. I also need to update existing records in tbl2 based on the [NumName] and [Size] values having changed in tbl1

Your help is much appreciated in advance.
Database10.accdb
0
Comment
Question by:databarracks
  • 7
  • 4
  • 2
13 Comments
 
LVL 9

Expert Comment

by:McOz
ID: 35006716
In order to update existing records in table 2, you need to include the primary key from table 1 as a foregin key in table 2, and set the relationship between the tables on that field. Otherwise there is no way to tell which record should be changed in table2 if you change the numname in table 1. See the attached file.

Try this for appending new records from table 1 to table 2:

INSERT INTO tbl2 ( Size, NumName )
SELECT tbl1.Size, tbl1.NumName
FROM tbl1
WHERE (((Exists (SELECT * FROM tbl2 WHERE tbl2.NumName=tbl1.NumName))=False));

To update existing records in tbl2 that have been changed in tbl1, use:

UPDATE tbl1 INNER JOIN tbl2 ON tbl1.ID = tbl2.Tbl1_ID SET tbl2.NumName = [Tbl1].[numname]
WHERE (((tbl2.NumName)<>[tbl1].[numname]) AND ((tbl2.tbl1_ID)=[tbl1].[ID]));

good luck!
0
 

Author Comment

by:databarracks
ID: 35006741
Sorry I never received the attachment?
0
 

Author Comment

by:databarracks
ID: 35006910
Could you please send the attachment if possible?
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 9

Expert Comment

by:McOz
ID: 35007409
oops! sorry about that, here is the file

McOz Database10.accdb
0
 
LVL 9

Accepted Solution

by:
McOz earned 125 total points
ID: 35007438
Also, this is the query to insert new records into tbl2 including tbl1.ID:

INSERT INTO tbl2 ( tbl1_ID, Size, NumName )
SELECT tbl1.ID, tbl1.Size, tbl1.NumName
FROM tbl1
WHERE (((Exists (SELECT * FROM tbl2 WHERE tbl2.NumName=tbl1.NumName))=False));

I did not include it in my first post. Sorry about the delay.

McOz
0
 

Author Comment

by:databarracks
ID: 35007973
Thank you so much please give me minute while I test this?
0
 

Author Comment

by:databarracks
ID: 35008140
Append works great but when I update the size in tbl1 and run the update query nothing happens?
0
 

Author Comment

by:databarracks
ID: 35008185
I noticed that you don't have the [Size] column in your update query. Should we be adding this, as we want to be able to update the [NumName] and the [Size] if they grow or decrease accordingly?
0
 

Author Comment

by:databarracks
ID: 35008288
I have changed the update query to
UPDATE tbl1 INNER JOIN tbl2 ON tbl1.ID = tbl2.Tbl1_ID SET tbl2.NumName = [Tbl1].[numname], tbl2.[Size] = [Tbl1].[Size]
WHERE (([tbl1].[ID]=[tbl2].[Tbl1_ID]));

Open in new window


And seems to update the sizes and Numnames, is this correct?It looks Ok but worried it updates everything instead of noly the areas where information has changed. Would this be a potential minefield or performance detractor when using large tables?
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 125 total points
ID: 35009050
You really don't need two queries to perform this. A single query will do!
Here is how:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23594743.html#a22089157

/gustav
0
 

Author Closing Comment

by:databarracks
ID: 35009125
Very helpful
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35009477
You are welcome!

/gustav
0
 
LVL 9

Expert Comment

by:McOz
ID: 35010701
sorry databarracks, I've been in and out...

if you include a couple checks on whether [numname] or [size] has changed in tbl1, the query will skip updating any records where they are the same (i.e. tbl2 is up to date). see attached.

Thanks for the points!

 
UPDATE tbl1 INNER JOIN tbl2 ON tbl1.ID = tbl2.Tbl1_ID SET tbl2.NumName = [Tbl1].[numname], tbl2.[Size] = [Tbl1].[Size]
WHERE [tbl1].[ID]=[tbl2].[Tbl1_ID] AND [tbl1].[numname]<>[tbl2].[numname] AND [tbl1],[size]<>[tbl2].[size];

Open in new window

0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question