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?