I have a master/detail relationship (one to many) set up between two tables. There always should be a matching record in each table. However since I know of no way to do this when adding a new record to the master table (this would be the ultimate), I am trying to setup an integrity report to show me records in the Master table that do not have at least one matching record in the detail table. But I am drawing a blank on the SQL query. The tables are
Master - _Clients
Detail - _Waitlog
key - iWaitlistID
Code I've tried (including several variations there of),
[codebox]select clients.`iWaitlistID`,
`iServicePtID`,
`vLastName`,
`vFirstName`,
`vCounty`,
`vIntakeWorker`,
`Status`,
`vUserName`
From `_Clients` clients
right outer join `_WaitLog` waitlog
on clients.iWaitListID = waitlog.iWaitListID
where not clients.iWaitListID = waitlog.iWaitListID
[/codebox]
Any suggestions?
Mike
