• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

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.
0
choppa_eye
Asked:
choppa_eye
  • 3
  • 2
1 Solution
 
derekkrommCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now