Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 20th, 2006, 07:28 PM
Registered User
Join Date: Dec 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Compare records between two tables

Hi there, I am fairly new to Access and VBA and I am trying to learn reading all your interesting posts. I have a table in access with 10 fields. Only field 2 is unique. The table has more than 60000 records and I receive a new table with a few hundred records to update the original table every week. The table that i receive every week has new records to add to the original table, and records already in the original table that need to be deleted. Field 1 on the updated table includes a letter A to add or D to delete.
I am trying to find a way to compare Field 2 in both tables and to have access identifying the new records and adding them to the original table and to delete those records in the original table that were marked to delete in the updated table.
I know that is somehow confusing but i am sure that somebody has a solution for this. thanks a lot for your help.

Reply With Quote
  #2 (permalink)  
Old December 21st, 2006, 05:35 AM
Friend of Wrox
Join Date: Sep 2003
Location: Salisbury, Wiltshire, United Kingdom.
Posts: 155
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to leehambly

So, to confirm... when you do the update you want to add new records and update existing records (by deleting the old version and add the new version).

Fairly simple, but you need to approach it in a piecemeal fashion, ie:

1. Remove the existing duplicated records first (this is a delete query)
2. Now all you need to do is add all the new records (append query)

1. You can identify and delete the existing duplicated records by linking on Field2 and deleting all that match, so something like:

"Delete from tblExisting where tblExisting.Field2 = tblNew.Field2"

2. Assuming you have a unique index on Field2, you can now simply add ALL records from your new table to the existing table, since you have already removed any duplicates, so something like:

"Insert into tblExisting (Field1, ... FieldN) Select Field1, ... FieldN from tblNew"

I do hope I have the right end of the stick!
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Compare 2 tables in 2 different DBs Feodorov SQL Server 2005 4 March 7th, 2008 08:08 AM
Compare two fields from different tables pallone SQL Server 2000 10 March 23rd, 2007 05:14 PM
how to compare tables data ramanadyv SQL Language 0 April 10th, 2006 04:38 AM
Compare retrieved records life_s Ng General .NET 2 April 11th, 2005 08:32 PM
Compare 2 tables.... Seb_soum Classic ASP Databases 2 June 4th, 2004 05:47 PM

All times are GMT -4. The time now is 03:00 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.