473,326 Members | 2,813 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

List box to list the opposite of a query

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
2 2182
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: simpleman | last post by:
Hi, I have an assignment that requires me to subtract two very large unsigned integers using linked list.AFAIK I have to manipulate data as character. But I dont know how to get input into the...
2
by: Waxabi X | last post by:
I created a view on an Access 2002 database through an ODBC query tool. The view works fine either through ODBC or the Access IDE. The problem I am having is that it does not show up in the Queries...
6
by: bradleyp | last post by:
Hi all, I have a table (tblCodes) that has two fields: 'ID' and 'Code'. A small portion of the table is as follows: |-------------| | tblCodes | |-------------| | ID | Code |...
4
by: dssuresh6 | last post by:
Whether browsing forward or backward can be done using a singly linked list. Is there any specific case where a doubly linked list is needed? For people who say that singly linked list allows...
44
by: Josh Mcfarlane | last post by:
Just out of curiosity: When would using std::list be more efficient / effective than using other containers such as vector, deque, etc? As far as I'm aware, list doesn't appear to be...
17
by: shades234 | last post by:
I saw a post about a year ago, that pondered if an opposite to a union query was possible, i.e. an intersect query. however, the OP was concerned about varous fields intersectiong with one another....
3
by: Geethu03 | last post by:
Hi I am writing a query to list some fields in asc,and desc.i want to list the field in my won order. that is my filed name is Status, in this i stored a values High, Low and Medium. I am able to...
7
by: macca | last post by:
You don't have to use DISTINCT on the full date, for example to get the distinct months use something like: SELECT DISTINCT MONTHNAME(date_field) AS Months or a PHP solution may use the...
1
by: Zhang Weiwu | last post by:
I know the subject line is really bad. Here is the real question: we have a web application that lists 1200 products. The web page present only 12 products at once, if you wish to see the next 12...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.