comparing records in 2 different tables

[KMangan]

Hi, I have 2 tables, and each table has a Product and Location field. One table is the master table containing all the Products and Locations. The second is a listing of what products in what locations are available. What I need to do is finding the Product, Location combinations that are in the master but aren't available. I was successful by using a SQL statement, however, I need the database to be a datafile and the SQL option isn't available. Here is what I had for the SQL statement to give a rough idea of what data I need to get.
SELECT
tblMissTemp.KeyName, tblMissTemp.Location
FROM
tblMissTemp
WHERE
tblMissTemp.KeyName+tblMissTemp.Location not in
(Select KeyName+Location from ReadEvents)

Any help or thoughts would be appreciated.

 

[skuhlman]

Not sure what database you're using... are you able to set up a file relation between the two files using both fields. I know for example that you can do this with foxpro tables. In foxpro the code would look something like this:

SELECT 0
USE TABLE2
INDEX ON T2FIELD1+T2FIELD2 TAG MYTAG
SELECT 0
USE TABLE1
SET RELATION TO T1FIELD1+T1FIELD2 INTO TABLE2

Once the relation is established, when scanning through TABLE1, any time TABLE2 is at End-of-File or the field in TABLE2 is NULL then you've got a record in TABLE1 that isn't in TABLE2. Setting the suppression of detail when a value exists in TABLE2 will than show only the mismatched records on your report.

Hope this helps