Comparing 2 Access Tables

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.
choppa_eyeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
derekkrommConnect With a Mentor Commented:
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
 
choppa_eyeAuthor Commented:
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
 
derekkrommCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
ghemstromCommented:
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
 
choppa_eyeAuthor Commented:
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
 
choppa_eyeAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.