Comparing two tables with query

[Dnphm]

Hi,

First let me say that I did not create this database but rather inherited from someone else.

I have two tables, "tblProductCurrent" & "tblProducts" which store information about our products and their policy numbers. Both tables have the same fields, "PolicyNum", "ProductName". "tblProductCurrent" contains the most accurate information as it is updated.

I am trying to compare the two tables to see if there is any difference in data. The "ProductName" is most likely the same for boh tables but the "PolicyNum" may be different. So, if the "PolicyNum" is different between the two tables, I would like the query to show it.



Hope that makes sense! Thanks!

[ajetrumpet]

what you should do is quuery the table that has the most records and then write something like this:

select * from table1

where productname in (select productname from table2)

and policynum <> 

dlookup("policynum", "table2", "productname = '" & productname & "'") 

that's oracle type code, so use the brackets as required by access

[weekend00]

If you are sure the productName of two table are match, you can query as following:

select tblProductCurrent.*, tblProduct.* from tblProductCurrent inner join tblProduct on tblProductCurrent.productName=tblProduct.productNa me where tblProductCurrent.policynum<>tblProduct.policynum