I’m trying to create a simple query to validate a range of records after they have been imported into my database. Also, I want to do this validation after the import, rather than rejecting the records upfront during the import; it’s just easier for someone to correct the few records when this happens than reimport the records with correct information later.
First a little background: I am using two tables – 1.) DATAtbl, containing all invoice information for each customer, and 2.) CUSTtbl, containing all customer information. CUST_ID from the CUSTtbl is looked up in the DATAtbl CUST_ID field, i.e. CUSTtbl records are one to the many DATAtbl records. CUST_ID in CUSTtbl is a prime key; it is not a prime key in DATAtbl.
The basic validation is to return any records from the specified range when the CUST_ID in the DATAtbl doesn’t match a CUST_ID record in the CUSTtbl, i.e. a CUST_ID as imported either doesn’t exist or was input incorrectly. The criteria is between a date range keyed off of the INV_DATE field. The fields to return are: 1.) INV_DATE and 2.) DATAtbl.CUST_ID
Any suggestions?
Thanks,
Scott
