Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 986
  • Last Modified:

Access Append and Update Query

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
databarracks
Asked:
databarracks
  • 7
  • 4
  • 2
2 Solutions
 
McOzCommented:
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
 
databarracksAuthor Commented:
Sorry I never received the attachment?
0
 
databarracksAuthor Commented:
Could you please send the attachment if possible?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
McOzCommented:
oops! sorry about that, here is the file

McOz Database10.accdb
0
 
McOzCommented:
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
 
databarracksAuthor Commented:
Thank you so much please give me minute while I test this?
0
 
databarracksAuthor Commented:
Append works great but when I update the size in tbl1 and run the update query nothing happens?
0
 
databarracksAuthor Commented:
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
 
databarracksAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
databarracksAuthor Commented:
Very helpful
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
 
McOzCommented:
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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 7
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now