Solved

Access Append and Update Query

Posted on 2011-03-01
13
964 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

737 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