DATA VALIDATION

[mrk2]

I would like to use a query to validate data and supply any non valid entries on a report. What I need suggestions on is how to compare data from a column in one table to data in the other table. For example I have a table with customers, each customer has a unique ID (1,2,3). The query list customers who have attended a class and the customers are identified by there customer id. Right now I have a query that looks at all entries in the "attendance" table that have a null entry in the customer ID. I would also like it to compare the ID's in "attendance" to the actual exisitng ID's in the customer table and if the ID does not exist then the record would be returned in the query.
thanks for any suggestions.
mrk

 

[jksmi]

Hi,

There is a wizard that helps you find records in one table that aren't in another, but it to find records in the attendance table that aren't in the customer table should be something like this...

SELECT tblAttendance.Customer_ID
FROM tblAttendance LEFT JOIN tblCustomers ON tblAttendance.Customer_ID = tblCustomers.Customer_ID
WHERE (((tblCustomers.Customer_ID) Is Null));

Hope this helps,

Jamie

 

[jksmi]

On second thoughts, you could stop invalid entries in the first place enforcing referential integrity in th erelationship or using a lookup for customer_id in the attendance table/ form...

Jamie