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
Password Reminder
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access 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 September 17th, 2008, 06:59 PM
Registered User
Join Date: Sep 2008
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Find difference between table and updated table

Hi- this is my first post here, and I'm new to using Access (2003), I hope someone can help.

I get massive data files that I load into a proprietary system and then updated files that have extra records or deleted records that I can easily find. However, there are over one hundred fifty fields and tens of thousands of records and I want to identify only the records that have changes in them.

Is there any way to identify the only records that have at least one change in them so I can work with only these records to make the updates?

Any help would be appreciated.

Reply With Quote
  #2 (permalink)  
Old September 18th, 2008, 03:05 PM
Friend of Wrox
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts

May I assume that you "updated files" are loaded into *separate* tables in the Access DB?

And may I assume that you have some way of knowing which records in the existing table correspond to which records in the new table (e.g, some sort of unique identifier)?

If so, it's not hard, but it's quite tedious.

You will need to do something like this:

FROM oldTable AS T1, newTable AS T2
WHERE T1.id = T2.id
AND ( T1.field1 <> T2.field1
      OR T1.field2 <> T2.field2
      OR T1.field3 <> T2.field3
      OR T1.field150 <> T2.field150 )

That code assumes that the "id" field is the one unique field (could be a combination of fields) that "connects" the two tables.

This will give you a list of the the id's of all the records that are not the same. Now, finding in what WAY the pairs of records are not the same is much more tedious, and pretty much has to be done on a per-field basis. It's a job that is better done in VBA or some program external to Access.
Reply With Quote
  #3 (permalink)  
Old September 20th, 2008, 04:25 PM
Registered User
Join Date: Sep 2008
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts

Thanks Old Pedant. You are correct that I get a new file and import it as a new table and there is a key field. I tried your query and it gave me exactly what I was looking for, ID where any one of the values in any one of the fields were different.

Thanks for helping me save time!

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
difference between row n table level triggers Hafiz Muhammad Mushtaq Oracle 1 July 9th, 2007 12:48 AM
Find Criteria in Table ayazhoda Access VBA 1 April 26th, 2007 09:03 AM
Replace and existing table with an updated one amerk20 Access VBA 6 August 30th, 2005 07:44 AM
find duplicate name on a table vickr1z Access 4 March 23rd, 2005 06:02 AM

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

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