Finding mis-matched records

[Paul Fenton]

We have an application that tracks client appointments and project
progress. In the Clients table, there's a check box to indicate that
an appointment was kept. The actual appointment records are in an
Appointments table and they linked by a unique ID for each client.

We've just discovered that it's possible to check the Appointment Kept
checkbox without there being an actual appointment record in the
appointments table. What happens then is that client record will
never show up on any of our reports for Pending Estimate, Follow Up,
etc. The record's there, but can't be located with the usual queries.

I've fixed the original problem and now I want to find all the record
in the Clients table where Appointments Kept is YES, but there is no
corresponding appointment record in the Appointments table.

I need some help with this one.


Paul Fenton

 

[Jeff Boyce]

Paul

Open the MDB into the Database window.

Click on the <NEW> button and select Query.

When the Query Wizard pops up, select the Unmatched query and follow the
wizard's steps.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Arvin Meyer [MVP]]

We have an application that tracks client appointments and project
progress. In the Clients table, there's a check box to indicate that
an appointment was kept. The actual appointment records are in an
Appointments table and they linked by a unique ID for each client.

We've just discovered that it's possible to check the Appointment Kept
checkbox without there being an actual appointment record in the
appointments table. What happens then is that client record will
never show up on any of our reports for Pending Estimate, Follow Up,
etc. The record's there, but can't be located with the usual queries.

I've fixed the original problem and now I want to find all the record
in the Clients table where Appointments Kept is YES, but there is no
corresponding appointment record in the Appointments table.

In your query, create an Outer Join (called a Left Join) in Access. Right
click on the Join between the 2 tables and choose the option that gives you
all of the records from the Clients table and the matching records from the
Appointments table, something like:

SELECT tblClients. *, tblAppointments.ClientID
FROM tblClients LEFT JOIN tblAppointments ON tblClients.ClientID =
tblAppointments.ClientID
WHERE (((tblAppointments.ClientID) Is Null));

 

[Paul Fenton]

Jeff and Arvin, thank you for the responses. Problem solved!

I hate it when I miss the obvious.


Paul Fenton