qry to show data from two tables

  • Thread starter Thread starter jane
  • Start date Start date
J

jane

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.
 
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.
 
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
 
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
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.
Click to expand...
.
Click to expand...
 
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
 
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



[email protected] said:
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
Click to expand...
.
Click to expand...
 
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.
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



[email protected] said:
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-----
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top