Solved

Comparing 2 Access Tables

Posted on 2011-03-02
6
243 Views
Last Modified: 2012-06-21
I have 2 identically formatted inventory tables in Access. One created yesterday and one created today.

The old one is AITCURRENT-2011-02-28

The one from today is AITCURRENT

I would like to know what has changed from AITCURRENT-2011-02-28 to today.

For example a field called STATUS could have changed from OnShelf to OnOrder - I need to know that field changed. Also the responsible manager could have changed in the TechContact field and so on.

Thanks in advance.
0
Comment
Question by:choppa_eye
  • 3
  • 2
6 Comments
 
LVL 15

Accepted Solution

by:
derekkromm earned 125 total points
ID: 35018358
Its not going to be a simple line of code.

You'll have to do something like this:

select * from ATICURRENT a inner join ATICURRENT-2011-02-28 b on
a.PK = b.PK
where
a.Field1 <> b.Field1
or a.Field2 <> b.Field2
.....
0
 

Author Comment

by:choppa_eye
ID: 35018424
what is PK?

The fields I'm working with here are:

TechName
Status
InvDate

that's it just 3 fields in both tables - looking for differences.

So with that would it be

select * from AITCURRENT a inner join AITCURRENT-2011-02-28 b on a.PK = b.PK where a.TechName <> b.TechName or a.Status <> b.Status or a.InvDate <> b.InvDate

Exactly as written?
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 35018449
PK is primary key

So whatever your Primary Key is on that table is how you should join the 2 tables. If you have an identity column called ID or something, you'd change the "PK" to "ID". Whatever uniquely identifies the records in your table.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 2

Expert Comment

by:ghemstrom
ID: 35018493
Thinking out of the box:

Why not add a timestamp column to each record. The timestamp being updated each time the record is saved. That way you just have to compare timestamps to find updated records.
0
 

Author Comment

by:choppa_eye
ID: 35018537
Derekkromm - That works, now I just have to figure out how to get it into a report so that it's easy to say

ID 1234 "changed from" On Shelf "to" On Order" :( Thanks for the help.
0
 

Author Closing Comment

by:choppa_eye
ID: 35018626
I didn't understand the short hand at first - a bit more instructional would have been better, but overall it got me where I needed to go.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

930 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now