How do I compare data in two tables

[DavPet]

Still needing an answer
I have two tables with identical structure but different data.
How can I compare every field in the table to its corresponding field in the
other table and give a result like :
[Report_no] " Entry date is different. Old value was " [date_entered]

 

[Guest]

The question for you is - how are you going to match up the rows in the table?
In other words, how will you know that the record in table 1 should match
the record in table 2?
Once you can identify that, the rest is easy.

 

[Guest]

How can I compare every field in the table
You then want a report that says dates are different. Does not match.

For flat comparrison an easy way is to concatenate all the fields and do one
compare.

 

[John Nurick]

Do the tables have a shared key field or fields that can be used to link
each record in one table to its counterpart in the other?

If so, create a select query that joins (inner join) the tables on the
key field(s) and returns all the fields you want to compare.

There are then two basic ways to go.

1) Write VBA code that opens a recordset on the query and iterates
through it record by record (using MoveNext inside a Do Until ... Loop
structure). Have code inside the loop that compares each field from the
first table with its counterpart from the second and takes action
accordingly.

2) Do it all in the query, by using a series of calculated fields each
consisting of an IIf() expression that compares a pair of fields. For
example, if the tables are aliased to A and B and the field is
date_entered, you might have

...., IIf(A.date_entered<> B.date_entered, "Entry date is different. Old
value was " & Format(A.EntryDate, "dd/mm/yyyy") & ".", ""), ...

Still needing an answer
I have two tables with identical structure but different data.
How can I compare every field in the table to its corresponding field in the
other table and give a result like :
[Report_no] " Entry date is different. Old value was " [date_entered]

 

[DavPet]

OK, now I have something to go on, but I still need help.

Klatuu - I do have a key that ties each record to its counterpart.
Karl - I kinda suspected your approach but have been unable to implement.
John - I like your VBA do-loop idea.

I can handle moving thru each record. How do I get each fields name for the
test part?

Do the tables have a shared key field or fields that can be used to link
each record in one table to its counterpart in the other?

If so, create a select query that joins (inner join) the tables on the
key field(s) and returns all the fields you want to compare.

There are then two basic ways to go.

1) Write VBA code that opens a recordset on the query and iterates
through it record by record (using MoveNext inside a Do Until ... Loop
structure). Have code inside the loop that compares each field from the
first table with its counterpart from the second and takes action
accordingly.

2) Do it all in the query, by using a series of calculated fields each
consisting of an IIf() expression that compares a pair of fields. For
example, if the tables are aliased to A and B and the field is
date_entered, you might have

..., IIf(A.date_entered<> B.date_entered, "Entry date is different. Old
value was " & Format(A.EntryDate, "dd/mm/yyyy") & ".", ""), ...

Still needing an answer
I have two tables with identical structure but different data.
How can I compare every field in the table to its corresponding field in
the
other table and give a result like :
[Report_no] " Entry date is different. Old value was " [date_entered]

 

[John Nurick]

I'd have thought that you'd know the names of the fields: after all, you
have the tables and the query<g>.

Anyway, a Recordset contains a Fields collection, and each Field has a
Name property. So if you've opened a recordset you can get the field
names, e.g.
Debug.Print rstR.Fields(0).Name

If the source of the recordset is a query like this, joining two
identically structured tables:

SELECT A.*, B.*
FROM TableOne AS A INNER JOIN TableOne AS B
ON A.KeyField = B.KeyField

the field names in the recordset will be like this
0 A.KeyField
1 A.Field2
2 A.Field3
3 A.Field4
4 B.KeyField
5 B.Field2
6 B.Field3
7 B.Field4
so it's easy to identify the fields you want to compare.

OK, now I have something to go on, but I still need help.

Klatuu - I do have a key that ties each record to its counterpart.
Karl - I kinda suspected your approach but have been unable to implement.
John - I like your VBA do-loop idea.

I can handle moving thru each record. How do I get each fields name for the
test part?

Do the tables have a shared key field or fields that can be used to link
each record in one table to its counterpart in the other?

If so, create a select query that joins (inner join) the tables on the
key field(s) and returns all the fields you want to compare.

There are then two basic ways to go.

1) Write VBA code that opens a recordset on the query and iterates
through it record by record (using MoveNext inside a Do Until ... Loop
structure). Have code inside the loop that compares each field from the
first table with its counterpart from the second and takes action
accordingly.

2) Do it all in the query, by using a series of calculated fields each
consisting of an IIf() expression that compares a pair of fields. For
example, if the tables are aliased to A and B and the field is
date_entered, you might have

..., IIf(A.date_entered<> B.date_entered, "Entry date is different. Old
value was " & Format(A.EntryDate, "dd/mm/yyyy") & ".", ""), ...

Still needing an answer
I have two tables with identical structure but different data.
How can I compare every field in the table to its corresponding field in
the
other table and give a result like :
[Report_no] " Entry date is different. Old value was " [date_entered]

 

[DavPet]

OK, the light is coming on now (getting brighter too).
Thank you for the guidance.

I'd have thought that you'd know the names of the fields: after all, you
have the tables and the query<g>.

Anyway, a Recordset contains a Fields collection, and each Field has a
Name property. So if you've opened a recordset you can get the field
names, e.g.
Debug.Print rstR.Fields(0).Name

If the source of the recordset is a query like this, joining two
identically structured tables:

SELECT A.*, B.*
FROM TableOne AS A INNER JOIN TableOne AS B
ON A.KeyField = B.KeyField

the field names in the recordset will be like this
0 A.KeyField
1 A.Field2
2 A.Field3
3 A.Field4
4 B.KeyField
5 B.Field2
6 B.Field3
7 B.Field4
so it's easy to identify the fields you want to compare.

OK, now I have something to go on, but I still need help.

Klatuu - I do have a key that ties each record to its counterpart.
Karl - I kinda suspected your approach but have been unable to implement.
John - I like your VBA do-loop idea.

I can handle moving thru each record. How do I get each fields name for
the
test part?

Do the tables have a shared key field or fields that can be used to link
each record in one table to its counterpart in the other?

If so, create a select query that joins (inner join) the tables on the
key field(s) and returns all the fields you want to compare.

There are then two basic ways to go.

1) Write VBA code that opens a recordset on the query and iterates
through it record by record (using MoveNext inside a Do Until ... Loop
structure). Have code inside the loop that compares each field from the
first table with its counterpart from the second and takes action
accordingly.

2) Do it all in the query, by using a series of calculated fields each
consisting of an IIf() expression that compares a pair of fields. For
example, if the tables are aliased to A and B and the field is
date_entered, you might have

..., IIf(A.date_entered<> B.date_entered, "Entry date is different. Old
value was " & Format(A.EntryDate, "dd/mm/yyyy") & ".", ""), ...



Still needing an answer
I have two tables with identical structure but different data.
How can I compare every field in the table to its corresponding field in
the
other table and give a result like :
[Report_no] " Entry date is different. Old value was " [date_entered]

 

[John Nurick]

SELECT A.*, B.*should of course be

FROM TableOne AS A INNER JOIN TableTwo AS B