Solved

Access Append and Update Query

Posted on 2011-03-01
13
932 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
 
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

948 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now