I am trying to compare tables

Jimee

Registered User.
Local time
Today, 17:19
Joined
May 4, 2001
Messages
21
I want an expression that will display data from fields only if they are different. Any suggestions? I have attached my database and the query is "A Query"


Thanks....
 

Attachments

Search the forums here, this kinda question is quite a regular one.

RV
 
There are many fields in your tables. Which fields do you want to compare?
 
Actually, I want to show the difference between the two tables.
 
It turns out you will need much more than an expression. In fact you will need three queries:

qryUnion:-
SELECT *
FROM A UNION Select * from B
ORDER BY REVA_ID;


qryDiff:-
SELECT *
FROM qryUnion
WHERE REVA_ID in (select REVA_ID from qryUnion group by REVA_ID having count(*)>1)
ORDER BY REVA_ID;


qryDiff_identifyingTheSourceTable
SELECT qryDiff.*, iif(qryDiff.Qty=A.Qty, "From table A","From table B") AS SourceTable
FROM B INNER JOIN (qryDiff INNER JOIN A ON (qryDiff.REVA_ID = A.REVA_ID) AND (qryDiff.ISO = A.ISO)) ON (B.REVB_ID = A.REVA_ID) AND (qryDiff.ISO = B.ISO)
ORDER BY qryDiff.REVA_ID, iif(qryDiff.Qty=A.Qty, "From table A","From table B");


Run the third query, which will show the records that are different in the two tables. (The query uses the field name REVA_ID even for the REVB_ID from table B, as the IDs must be put under one column in the Union query.)
 
That is close. What I need, is for this query to tell me what is different about the two tables.
1. If something is in one but not the other.
2. If a quantity has changed.

Basically if there are any differences. I have attached the database with some differences.
 

Attachments

Hi,

Is this what you want? Bring up all records in TableA that contains a [DocID] that's not in TableB.

If so, you first need to link the [DocID] field in both tables, creating a join to include all records from TableA and only records in TableB where values are equal.

Then select the fields you want to be displayed from TableA and where [TableB].[DocID] is NULL

Hope that makes sense!
 
Jimee,

"I have attached the database with some differences."
It is an understatement. You have attached a database with a great difference.

REVA_ID in table A is an AutoNumber field. Numbers generated by an AutoNumber field cannot be edited or modified. Why has every number in the field changed all of a sudden in your new mdb file? And why does none of the IDs in the two tables now match?

If the IDs can be changed at will, what is the purpose of your (B.REVB_ID = A.REVA_ID) in your original query?:-

SELECT DISTINCTROW A.ISO, A.Spec, B.ISO, B.Spec, B.Fitting, B.Qty, B.SizeStr, B.Fitting_Sub_Type, B.Rating, B.Facing, B.Desc, B.Matl, B.Sch, B.Ends, B.Tag, B.REVB_ID, A.Fitting, A.Qty, A.SizeStr, A.Fitting_Sub_Type, A.Rating, A.Facing, A.Desc, A.Matl, A.Sch, A.Ends, A.Tag, A.REVA_ID
FROM B INNER JOIN A ON (B.ISO = A.ISO) AND (B.REVB_ID = A.REVA_ID);

Do you know that you have defeated your own query?

Is the whole thing a real-world problem or just a brain teaser?
 
Last edited:
It is a real world challenge. Ther Rev_id is not really a prt of the orriginal database. Is it possible to do what is needed without that table. I'll send a virgin copy.
1. There are 2 tables with Identical fields. There are no unique fields, but one is from a database from some point about a mont ago, and the other is from now.
2. Some data from A have been either deleted or modified relative to B.
3. I need to identify any differences between A and B and generate a report to identify those differences. I can handle the report side.
 

Attachments

Users who are viewing this thread

Back
Top Bottom