Solved

Access Append and Update Query

Posted on 2011-03-01
13
968 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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 50

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 50

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

688 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