By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,972 Members | 1,454 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,972 IT Pros & Developers. It's quick & easy.

Comparing to Tables and creating 2 new tables with result

P: n/a
Im importing two spreadsheet into two access tables (tblnew and tblold) and i want to compare the first column called "Name" in each table and create/populate two new tables (tblsame and tbldif) Where the field "Name" matches in both tblnew and tblold i want that record copied to the table tblsame and if a "name" exists in one of the tables but not the other then i want this record copied into a new table tbldif.

I havent played with access much and finding it hard to understand how to create such queries, so would welcome step by step help if possible.

Thanks for any advice you can give
Sep 29 '10 #1
Share this Question
Share on Google+
8 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
You can create 3 queries to get the relevant records and then change them to append queries to append them to the relevant tables.

Expand|Select|Wrap|Line Numbers
  1. SELECT tblNew.* 
  2. FROM tblNew INNER JOIN tblOld
  3. ON tblNew.Name = tblOld.Name
This will give all records that are the same in both tables

Expand|Select|Wrap|Line Numbers
  1. SELECT tblNew.* 
  2. FROM tblNew LEFT JOIN tblOld
  3. ON tblNew.Name = tblOld.Name
  4. WHERE tblOld.Name Is Null
This will give all records present in tblNew but not in tblOld

Expand|Select|Wrap|Line Numbers
  1. SELECT tblOld.*
  2. FROM tblOld LEFT JOIN tblNew
  3. ON tblOld.Name = tblNew.Name
  4. WHERE tblNew.Name Is Null
This will give all records present in tblOld but not in tblNew
Sep 29 '10 #2

P: n/a
Thanks for the quick response - i will give this a try shortly and see what luck i have

Cheers
Sep 29 '10 #3

P: n/a
Oh one thing - dump question sorry - how do i enter this code into access?
Sep 29 '10 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Open a new query in design view. Then change the view to SQL. Copy in the code for the first query. The change back to design view and change the query type to Append. It will ask you for the name of the table to append to.
Sep 29 '10 #5

P: n/a
Thanks so much MMcCarthy - works a treat :)
Sep 30 '10 #6

P: n/a
Hmmm I thought it worked, but just clearing the compare tables down im now getting "Duplicate output destination 'Name'. when i run the second and third queries.
Sep 30 '10 #7

P: n/a
Right I have really lost it - Im going Mad - it is working fine :)

Sorry im being an idiot

Thanks Again
Sep 30 '10 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
No problem. I'm glad you've got it working.
Sep 30 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.