Differences between two tables 2

[netrusher]

I have a Table named WorkUnit1234567-01-01-07. We'll say the info on this table is from 1 January. I will then have a Table named WorkUnit1234567-02-01-07. We'll say the info on this table is from 1 February. Both of these tables will have the same fields. What I would like to do is compare Table WorkUnit1234567-01-01-07 with WorkUnit1234567-02-01-07 and only show the differences between the first table and the last table. Any ides on how to accomplish this?

 

[Remou]

It depends on how many fields you want to compare, if there are only a few, I think a query will be easiest. If there are a lot of fields, it may be easier to use VBA to loop through the recordsets. This is assuming that you have an ID to join these tables.

 

[netrusher]

I want to compare all fields in the table because I have trying to find all changes between the two. How do you do the VBA loop you spoke about?

 

[lespaul]

how many fields are in the table? 15 or 150?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[netrusher]

10

 

[Remou]

With 10 fields a query is easier. Something like:

[tt]Select * From tblA
Inner Join tblB On tblA.ID=tblB.ID
Where Nz(tblA.FieldA,"") <> Nz(tblB.FieldA,"")
Or Nz(tblA.FieldB,"") <> Nz(tblB.FieldB,"")
Or ...
And so on.[/tt]

You may also wish to check for records that are missing from either table.

 

[netrusher]

Thank you Remou.

I also would like to possibly come up with the following results. Any advice there

-In target not in source
-In source not in target
-In target and source, but different

 

[Remou]

You can use a function (UDF) in your query:

Function CheckStat(fldA, fldB)

fldA = Nz(fldA, "")
fldB = Nz(fldB, "")

If fldA = fldB Then
    CheckStat = "Equal"
    Exit Function
End If

If fldA = "" And fldB <> "" Then
    CheckStat = "In TableB"
    Exit Function
End If

If fldA <> "" And fldB = "" Then
    CheckStat = "In TableA"
    Exit Function
End If

If fldA <> "" And fldB <> "" Then
    CheckStat = "Not Equal"
    Exit Function
End If

End Function

 

[PHV]

A starting point:
SELECT 'Diff' AS Result, A.*, B.*
FROM [WorkUnit1234567-01-01-07] AS A
INNER JOIN [WorkUnit1234567-02-01-07] AS B ON A.ID = B.ID
WHERE A.field1 <> B.field1 OR A.field2 <> B.field2 OR ... OR A.field10 <> B.field10
UNION ALL SELECT 'Only A' AS Result, A.*, B.*
FROM [WorkUnit1234567-01-01-07] AS A
LEFT JOIN [WorkUnit1234567-02-01-07] AS B ON A.ID = B.ID
WHERE B.ID Is Null
UNION ALL SELECT 'Only B' AS Result, A.*, B.*
FROM [WorkUnit1234567-01-01-07] AS A
RIGHT JOIN [WorkUnit1234567-02-01-07] AS B ON A.ID = B.ID
WHERE A.ID Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[netrusher]

What is meant by?:

This is assuming that you have an ID to join these tables

 

[PHV]

The real question is:
Given a row from a table how do you know which row from another table you have to compare to ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[netrusher]

Below is the code I am using. I am taking the exact two tables just with different names. Each table has 606 rows of exactly the same information. My query produces over 16,000 rows with "Both but Different" results. What am I doing wrong?

SELECT 'Both but Different' AS Result, A.*, B.*
FROM [ROYSPEC1] AS A
INNER JOIN [ROYSPEC2] AS B ON A.PRODUCT = B.PRODUCT
WHERE A.EVENT_NUMB <> B.EVENT_NUMB OR A.EVENT_TIME <> B.EVENT_TIME OR A.OPERATION_ <> B.OPERATION_
UNION ALL SELECT '1Old' AS Result, A.*, B.*
FROM [ROYSPEC1] AS A
LEFT JOIN [ROYSPEC2] AS B ON A.PRODUCT = B.PRODUCT
WHERE B.PRODUCT Is Null
UNION ALL SELECT '2New' AS Result, A.*, B.*
FROM [ROYSPEC1] AS A
RIGHT JOIN [ROYSPEC2] AS B ON A.PRODUCT = B.PRODUCT
WHERE A.PRODUCT Is Null;

 

[netrusher]

The Product can be in each table multiple times. Is this causing a problem?

 

[PHV]

Yes, you have to have a combination of fields that uniquely identify each row.
BTW, no primary key ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[netrusher]

I am downloading these tables as DBF files. How can I add a primary key that will help my situation?

 

[Remou]

Ideally, look for a combination of fields that makes each record unique and match-able. For example, product + date ordered + customer. Alternatively, I seem to recall (it has been a while) that DBF files have a means of sorting by the order of entry. If this is the same for both tables, you could output with the recordnumber.

 

[netrusher]

Also,

If it is in both but different how can I get it to only show me the field that is DIFFERENT?

 

[Remou]

You can use the function I supplied:

[tt]SELECT a.Field1, b.Field1,<...>,a.FieldX,b.FieldX
FROM ROYSPEC1 AS A INNER JOIN ROYSPEC2 AS B ON A.ID = B.ID
WHERE CheckStat(a.Field1,b.Field1)="Not Equal"
<...>
OR CheckStat(a.FieldX,b.FieldX)="Not Equal"[/tt]

The function should be pasted into a module.