list all records in table 1 even though no record in table 2

[Guest]

I have a db for a new music teaching business. It includes the following
tables/fields:

tblMusicTeaching (with student details)
strSurname, etc
intLessonTime
intLessonDay

tblTimesOfTheDay (schedule of ½ hour slots for music lessons)
intTimeID
strLessonTime

tblDaysOfTheWeek
intDayID
strLessonDay

I can link those tables in a query that creates an accurate timetable except
for one issue - no gaps. I can produce this…

9.00 am - student 1
10.00 am - student 2

what I would prefer but can’t create is…

9.00 am - student 1
9.30 (blank)
10.00 am - student 2
(ie, listing the time even though there is no student record with that time)

I’m aware of the option of setting join types in Table relationships, but it
didn’t seem to do anything - I presume because I don’t have a table/sub-table
structure?

Access 2002
windows xp

 

[Duncan Bachen]

I have a db for a new music teaching business. It includes the following
tables/fields:

tblMusicTeaching (with student details)
strSurname, etc
intLessonTime
intLessonDay

tblTimesOfTheDay (schedule of ½ hour slots for music lessons)
intTimeID
strLessonTime

tblDaysOfTheWeek
intDayID
strLessonDay

I can link those tables in a query that creates an accurate timetable except
for one issue - no gaps. I can produce this…

9.00 am - student 1
10.00 am - student 2

what I would prefer but can’t create is…

9.00 am - student 1
9.30 (blank)
10.00 am - student 2
(ie, listing the time even though there is no student record with that time)

I’m aware of the option of setting join types in Table relationships, but it
didn’t seem to do anything - I presume because I don’t have a table/sub-table
structure?

Access 2002
windows xp

You don't have to predefine your relationships, it just makes it easier
in your query grid in the future.

In your case, you need to create a Left Join which will return all the
records in the tblTimesofDay table, whether or not there is an
associated appointment.

You do this by double-clicking on the join line between your two tables.

Are your field names above just samples (meaning you have additional
fields) or are they the only ones?

I'm asking because there doesn't appear to be any correlation between
the times of the day and the days of the week.

In order words, does your Days of the Week table only have 7 entries (1
for each day), and your Time Table have entries for each available time
slot? Suppose you have different time slots for different days?

Are you checking for conflicts? What makes Tuesday 9am this week
different than Tuesday 9am next week?

I'm raising these questions because I want to make sure you are tracking
your available slots in the best way.