Finding mis-matched records

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

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

"Paul Fenton" wrote in message
news
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

"Paul Fenton" wrote in message
news
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));

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

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

I hate it when I miss the obvious.


Paul Fenton