Macro to compare two tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AlanK - 11 Sep 2006 14:28 GMT

Hello,

I am a relative novice at VB.
I am currently trying to write a macro to compare two tables.
The tables have the exact same columns and I save the table once a month.
I want to be able to compare the new and the original databases.
Any ideas in how to go about this?

Thank You.

Reply to this Message

Steve Schapel - 11 Sep 2006 21:42 GMT

Alan,

If I understand you correctly, you would make a query, which includes
both tables, with a Left Join on the key field(s) from the "new" to the
"old", and put Is Null in the criteria of the "old" table field. This
will give all records that exist in the new table that do not exist in
tot old table. Is that what you want? If not, can you explain what you
mean by "compare two tables"? Some examples would help.

Signature

Steve Schapel, Microsoft Access MVP

> Hello,
>
[quoted text clipped - 5 lines]
>
> Thank You.

Reply to this Message

AlanK - 12 Sep 2006 08:46 GMT

Thanks for you responce Steve.

I would like to explain this a bit more.

I have my master table called "Design Index".
Within "Design Index" I have 10 columns (Tag Number, Size, Material ...etc).
The index has 600 entries (rows) and "Tag Number" is the Primary Key.

At different stages of the Project I will Issue Design Index, for example
today's Issue
may be called "Design Index Rev A 12 Sept 2006".

I will save a copy of "Design Index" in Tables called "Design Index Rev A 12
Sept 2006".

The table "Design Index" remains the master and any additions (i.e. new
rows) or changes go into this table.

At a later stage I will want to issue "Design Index" again and save a copy
in tables called "Design Index Rev B 10 Dec 2006".

I need a facility to identify the changes between tables "Design Index Rev A
12 Sept 2006" & "Design Index Rev B 10 Dec 2006"
i.e. what values have changed and what new rows have been added.

Any ideas?

Thanks,

Alan.

> Alan,
>
[quoted text clipped - 14 lines]
> >
> > Thank You.

Reply to this Message

Steve Schapel - 12 Sep 2006 09:25 GMT

Alan,

I should mention that you are not really using a relational database
relationally, and I think the general consensus would be that this is
not a valid design.

Nevertheless, leaving this question aside, the basic thrust of my
earlier suggestion still stands, i.e. a query joining the two tables to
be compared will give the results you want. The SQL view of such a
query might look something like this...

SELECT [2ndTable].[Tag Number], [1st Table].[Tag Number] Is Null AS
NewlyAdded, [1st Table].[Size]=[2nd Table].[Size] AS [SameSize], [1st
Table].[Material]=[2nd Table].[Material] AS [SameMaterial], ... etc
FROM [2nd Table] LEFT JOIN [1st Table] ON [2ndTable].[Tag Number]=[1st
Table].[Tag Number]

Let us know whether that starts to look liuke the outcome you are after.

Signature

Steve Schapel, Microsoft Access MVP

> Thanks for you responce Steve.
>
[quoted text clipped - 20 lines]
> 12 Sept 2006" & "Design Index Rev B 10 Dec 2006"
> i.e. what values have changed and what new rows have been added.

Reply to this Message

AlanK - 14 Sep 2006 08:06 GMT

Thanks Steve, this works fine.

> Alan,
>
[quoted text clipped - 39 lines]
> > 12 Sept 2006" & "Design Index Rev B 10 Dec 2006"
> > i.e. what values have changed and what new rows have been added.

Reply to this Message

knightconsulting@gmail.com - 14 Sep 2006 17:06 GMT

if you're really lazy you can try Firefly as well. I wrote it and yes,
it's free.

http://www.getfirefly.net/
http://www.getfirefly.net/FF_Screen_1_Small_4.png

James

Reply to this Message