List box to list the opposite of a query

[ormy28]

I really need some help with the following problem if anyone would be
willing.

I need a list box to list the opposite of what appears in a query.
Heres the details:

My database is for a doctors surgery, and this includes the tables
tblPatients, tblDoctors and tblAppointments . tblAppointments has an
Appointment ID and also includes the Patient ID (from tblPatient) and
Doctor ID (from tblDoctor) plus the start and end time of the
appointment.

tblAppointment( APPOINTMENT_ID, patient ID, Doctor ID, start time, end
time)

What I want to do is create a list box that lists all the Doctor ID's
for doctors who do not have an appointment at a given time. this time
is determined when the user types in a start and end time on a form. I
have created a query which lists all the Doctor ID's that do have an
appointment at that time. this query is based on the tblAppointment.
what I now want to do is create a list box that will list all the
Doctor ID's that do not appear in this query.

Sounds pretty basic stuff I know, but I've been trying for days with
NOT arguments and many other methods, but had no success.

[rkc]

ormy28 wrote:

I need a list box to list the opposite of what appears in a query.
Heres the details:

My database is for a doctors surgery, and this includes the tables
tblPatients, tblDoctors and tblAppointments . tblAppointments has an
Appointment ID and also includes the Patient ID (from tblPatient) and
Doctor ID (from tblDoctor) plus the start and end time of the
appointment.

tblAppointment( APPOINTMENT_ID, patient ID, Doctor ID, start time, end
time)

What I want to do is create a list box that lists all the Doctor ID's
for doctors who do not have an appointment at a given time. this time
is determined when the user types in a start and end time on a form. I
have created a query which lists all the Doctor ID's that do have an
appointment at that time. this query is based on the tblAppointment.
what I now want to do is create a list box that will list all the
Doctor ID's that do not appear in this query.

You haven't posted your query so I'll just post an example based on
the Employees table of the Northwind database. Basically you select all
the DoctorID's from the Doctor table that aren't returned by the
query you already have working.

SELECT Employees.Emplo yeeID
FROM Employees
WHERE EmployeeID Not IN
(
SELECT Employees.Emplo yeeID
FROM Employees
WHERE Employees.Emplo yeeID=1
)

[Darryl Kerkeslager]

I would consider this option to simplify things:

Set up a table of appointment slots in 15 minute intervals, or whatever may
be appropriate in your office. I'm assuming that the appointments are of
long enough daration, and of a duration that cannot be pre-determined, such
that you would not typically have appointment times of "2:35 pm - 2:45 pm" ,
but that your appointments would be more on the order of 15 minute or
half-hour increments. So you might have a table like:

appt_slot_ id as_start_time as_end_time
1 8:00: AM 8:15 AM
2 8:15 AM 8:30 AM

etc.

Then, your tblAppointemnt is not dealing with times, just the IDs, which
will simplify your queries. Further, If you can eliminate the user entering
invalid times, then I would (using a combobox rahter than textbox). Of
course this would create a restriction from entering appointment times at
odd intervals - no 2:35 appointments. This may not be acceptable, so this
may not be useful.

Darryl Kerkeslager
"ormy28" <ma*******@hotm ail.com> wrote:

I really need some help with the following problem if anyone would be
willing.

I need a list box to list the opposite of what appears in a query.
Heres the details:

My database is for a doctors surgery, and this includes the tables
tblPatients, tblDoctors and tblAppointments . tblAppointments has an
Appointment ID and also includes the Patient ID (from tblPatient) and
Doctor ID (from tblDoctor) plus the start and end time of the
appointment.

tblAppointment( APPOINTMENT_ID, patient ID, Doctor ID, start time, end
time)

What I want to do is create a list box that lists all the Doctor ID's
for doctors who do not have an appointment at a given time. this time
is determined when the user types in a start and end time on a form. I
have created a query which lists all the Doctor ID's that do have an
appointment at that time. this query is based on the tblAppointment.
what I now want to do is create a list box that will list all the
Doctor ID's that do not appear in this query.

Sounds pretty basic stuff I know, but I've been trying for days with
NOT arguments and many other methods, but had no success.