By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,454 Members | 2,633 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,454 IT Pros & Developers. It's quick & easy.

List box to list the opposite of a query

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.EmployeeID
FROM Employees
WHERE EmployeeID Not IN
(
SELECT Employees.EmployeeID
FROM Employees
WHERE Employees.EmployeeID=1
)
Nov 13 '05 #2

P: n/a
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*******@hotmail.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.

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.