qry to show data from two tables

I am trying to build an appointments database which
includes a table of appointment times spaced at fifteen
minute intervals and a table to record appointments made,
each appointment will either be for 15 or 30 minutes. I
need to be able to build a form based on a query which
will show all appointment times for a specific date and
information from the appointments details table where they
exist. Is this possible? If anyone can help with this I
would be very grateful.

It should be. The query would use a Left or Right join

SELECT A.AppointmentTime, B.*
FROM PossibleAppointmentTimes as A LEFT JOIN
RealAppointments as B
ON A.AppointmentTime = B.AppointmentTime
WHERE B.AppointmentDate = #6/6/04#

That depends on your appointment Date and Appointment Time being in different
fields and you have two records for any appointment that lasts 30 minutes. This
may or may not work, since you posted NO details of your fields and data structure.

Replace the table names and field names above with your table names and field names.

jane wrote:

I am trying to build an appointments database which
includes a table of appointment times spaced at fifteen
minute intervals and a table to record appointments made,
each appointment will either be for 15 or 30 minutes. I
need to be able to build a form based on a query which
will show all appointment times for a specific date and
information from the appointments details table where they
exist. Is this possible? If anyone can help with this I
would be very grateful.

Thank you for your help, I have tried the suggested query
but with no luck, I have included the table structures
below.

Table name Field name Data type
Table 1 Tbltime time short time (list of times)

Table 2 tblevent eventID autonumber
Appointment text (Name of
Appointment)
Adate short date (date of
Appointment)
Time short time (time of
Appointment)
SID number (staff number)

Table 1 holds the possible appointment times (31 records
with times at 15 min intervals) and table 2 holds a list
of booked appointments and their details. I want to list
appointment details for a chosen date and where no appoint
ment has been made for any of the available times in table
1 this should show as a time with no details as below. I
hope you can follow this and see where my problem is.
Thanks again
Jane

Required query results:

Time Appointment Sid
9:15
9:30 Mr Smith 1
9:45 Miss Jones 1
10:00
10:15 John Mats 1
10:30

and so on.

-----Original Message-----
It should be. The query would use a Left or Right join

SELECT A.AppointmentTime, B.*
FROM PossibleAppointmentTimes as A LEFT JOIN
RealAppointments as B
ON A.AppointmentTime = B.AppointmentTime
WHERE B.AppointmentDate = #6/6/04#

That depends on your appointment Date and Appointment
Time being in different
fields and you have two records for any appointment that
lasts 30 minutes. This
may or may not work, since you posted NO details of your
fields and data structure.

Replace the table names and field names above with your
table names and field names.

jane wrote:

I am trying to build an appointments database which
includes a table of appointment times spaced at fifteen
minute intervals and a table to record appointments
made,
each appointment will either be for 15 or 30 minutes. I
need to be able to build a form based on a query which
will show all appointment times for a specific date and
information from the appointments details table where
they
exist. Is this possible? If anyone can help with this I
would be very grateful.
.

Well, I think the query should look something like the following. If this is
not working, can you explain how it is failing? Are you not getting any rows
returned; are you getting rows but you can't update; are you getting only the
blank rows; only the rows with actual appointments? An error message of some kind?

SELECT tblTime.[Time], tblEvent.*
FROM tblTime LEFT JOIN tblEvent
ON TblTime.[Time] = tblEvent.[Time]
WHERE ADate = #6/1/04#

Jane wrote:

Thank you for your help, I have tried the suggested query
but with no luck, I have included the table structures
below.

Table name Field name Data type
Table 1 Tbltime time short time (list of times)

Table 2 tblevent eventID autonumber
Appointment text (Name of
Appointment)
Adate short date (date of
Appointment)
Time short time (time of
Appointment)
SID number (staff number)

Table 1 holds the possible appointment times (31 records
with times at 15 min intervals) and table 2 holds a list
of booked appointments and their details. I want to list
appointment details for a chosen date and where no appoint
ment has been made for any of the available times in table
1 this should show as a time with no details as below. I
hope you can follow this and see where my problem is.
Thanks again
Jane

Required query results:

Time Appointment Sid
9:15
9:30 Mr Smith 1
9:45 Miss Jones 1
10:00
10:15 John Mats 1
10:30

and so on.

Thanks for your continued efforts, I have copied across
the suggested SQL and pasted it into a new query. When the
query is opened a dialog box requests "Adate" be supplied.
If you supply a date the query returns no records as it
does if you leave this date blank. I have tried placing
the date parameter against the start field, this returns
the recorded appointments only. I hope this helps in
identifying the problem.
Thanks again
Jane
-----Original Message-----
Well, I think the query should look something like the
following. If this is
not working, can you explain how it is failing? Are you
not getting any rows
returned; are you getting rows but you can't update; are
you getting only the
blank rows; only the rows with actual appointments? An
error message of some kind?

SELECT tblTime.[Time], tblEvent.*
FROM tblTime LEFT JOIN tblEvent
ON TblTime.[Time] = tblEvent.[Time]
WHERE ADate = #6/1/04#

Jane wrote:

Thank you for your help, I have tried the suggested
query
but with no luck, I have included the table structures
below.

Table name Field name Data type
Table 1 Tbltime time short time (list of
times)

Table 2 tblevent eventID autonumber
Appointment text (Name of
Appointment)
Adate short date (date of
Appointment)
Time short time (time of
Appointment)
SID number (staff number)

Table 1 holds the possible appointment times (31 records
with times at 15 min intervals) and table 2 holds a list
of booked appointments and their details. I want to list
appointment details for a chosen date and where no
appoint
ment has been made for any of the available times in
table
1 this should show as a time with no details as below.
I
hope you can follow this and see where my problem is.
Thanks again
Jane

Required query results:

Time Appointment Sid
9:15
9:30 Mr Smith 1
9:45 Miss Jones 1
10:00
10:15 John Mats 1
10:30

and so on.
.

If you are being asked for ADate then the field must not exist or is misspelled.
I also left off the second part of the where clause.

SELECT tblTime.[Time], tblEvent.*
FROM tblTime LEFT JOIN tblEvent
ON TblTime.[Time] = tblEvent.[Time]
WHERE TblEvent.[ADate] = #6/1/04# OR
TblEvent.[ADate] Is Null



wrote:

Thanks for your continued efforts, I have copied across
the suggested SQL and pasted it into a new query. When the
query is opened a dialog box requests "Adate" be supplied.
If you supply a date the query returns no records as it
does if you leave this date blank. I have tried placing
the date parameter against the start field, this returns
the recorded appointments only. I hope this helps in
identifying the problem.
Thanks again
Jane
-----Original Message-----

Thanks, but the query is still not right. The query
returns the data as expected apart from where an
appointment is booked on any other day, the time at which
the appointment is logged at does not appear. e.g. test
data is placed in tblevent showing one appointment on
26/06/04 at 9:15 and one other appointment on 27/06/04 at
12:00. The query date is set to 26/06/04, the query
returns details of the appointment at 9:15 and all other
available times from the time table apart from 12:00 the
date of the appointment on the 27/06/04.

Thanks again.
Jane
-----Original Message-----
If you are being asked for ADate then the field must not
exist or is misspelled.
I also left off the second part of the where clause.

SELECT tblTime.[Time], tblEvent.*
FROM tblTime LEFT JOIN tblEvent
ON TblTime.[Time] = tblEvent.[Time]
WHERE TblEvent.[ADate] = #6/1/04# OR
TblEvent.[ADate] Is Null



wrote:

Thanks for your continued efforts, I have copied across
the suggested SQL and pasted it into a new query. When
the
query is opened a dialog box requests "Adate" be
supplied.
If you supply a date the query returns no records as it
does if you leave this date blank. I have tried placing
the date parameter against the start field, this returns
the recorded appointments only. I hope this helps in
identifying the problem.
Thanks again
Jane
-----Original Message-----
.

OK, I am doing something wrong here, but I'm not sure what. I hesitate to
suggest this, but if you are using Access97 or Access2000 and can zip up the
database and email it to me, I will take a look. BEFORE you do,

Compact the database
Then Zip it if you can. I really don't want to try to download a really big
file. If it is less than a megabyte, you can try to Mail it to

S P E N C E R AT you em bee sea.EDU

Remove the spaces and replace the words with the letters and replace the "at"
with the relevant symbol.

Jane wrote:

Thanks, but the query is still not right. The query
returns the data as expected apart from where an
appointment is booked on any other day, the time at which
the appointment is logged at does not appear. e.g. test
data is placed in tblevent showing one appointment on
26/06/04 at 9:15 and one other appointment on 27/06/04 at
12:00. The query date is set to 26/06/04, the query
returns details of the appointment at 9:15 and all other
available times from the time table apart from 12:00 the
date of the appointment on the 27/06/04.

Thanks again.
Jane
-----Original Message-----
If you are being asked for ADate then the field must not
exist or is misspelled.
I also left off the second part of the where clause.

SELECT tblTime.[Time], tblEvent.*
FROM tblTime LEFT JOIN tblEvent
ON TblTime.[Time] = tblEvent.[Time]
WHERE TblEvent.[ADate] = #6/1/04# OR
TblEvent.[ADate] Is Null



wrote:

Thanks for your continued efforts, I have copied across
the suggested SQL and pasted it into a new query. When
the
query is opened a dialog box requests "Adate" be
supplied.
If you supply a date the query returns no records as it
does if you leave this date blank. I have tried placing
the date parameter against the start field, this returns
the recorded appointments only. I hope this helps in
identifying the problem.
Thanks again
Jane
-----Original Message-----
.