Macro to compare two tables

[AlanK]

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.

 

[Steve Schapel]

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.

 

[AlanK]

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.

 

[Steve Schapel]

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.

 

[AlanK]

Thanks Steve, this works fine.

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.

--
Steve Schapel, Microsoft Access MVP

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.

 

[knightconsulting]

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