I posted a question about an appointment system I am building and the response I got told me my design was flawed.

I restarted with a new design but I run into one problem so I am posting all new:

          **************************
I am building an appointment calendar. I have a main form called frmAppointmentDay and a subform called frmAppointmentDetails in 1 to Many relationship.

Thes subform DefaultView is continuous Form. The main form has these fields:
AppointmentDayID - AutoNumber PK
AppointmentDate, datatype Date/Time.

The subform has the following fields:
AppointmentDetailsID - AutoNumber and PK
AppointmentDayID - Number
Comments - memo
PatientID - Number (lookup from tblPatient)
AppointmentTime - Text

Since this is an appointment program each time a new appointment day is selected and saved in the main form I would like to automatically populate 67 records in frmAppointmentDetails. This 67 records arise because appointments are in increment of 10 minute intervals starting form 8:00 (am) to 7:00 (pm)

The AppointmentTime field of the first record record would be 8:00, the second record 8:10, third record 8:20; fourth record 8:30 etc. This would continue in increment of 10 minutes to 7:00 (pm).

I want it this way so that the user would have a visual layout of all the appointment slots for that day. So if an appointment is for 8:30 to 10:00 then the user would select the patient name for 8:30, 9:30 and 10:00, the patient name is displayed three times (three different records)in the subform.

If I can autopoputate the AppointmentTime slots each time a new Appointment date is selected then it would display all appointment slots and the user can simply assign patients to any vacany time slot.

How can I auto populate these 67 records as explained.

Note also I realise that is easier to make the AppointmentTime a text field rather than a Date/Time as I would not have to format the date to ShortTime.
I tried to be a clear as possible and hope I was able to do so.

Thanks for any help

tim

Tim,
   Here is a possible solution. Use a cartesian product query when a new day is selected to auto populate the records. Here is the SQL statement I created and it works like a champ.

INSERT INTO tblAppointmentDateDetails ( AppointmentDayID, AppointmentTime )
SELECT tblAppointmentDates.AppointmentDayID, tblTime.Time
FROM tblTime, tblAppointmentDates
WHERE (((tblAppointmentDates.AppointmentDate)=#2/6/2004#));

tblTime is a table that contains a single text field. each record contains a time. Notice that in the from clause, there is no join. This is what makes it a cartesian product. Make sure you use the where clause to specify a date. Otherwise you will be creating 67 records for each day in the database.

Hope this helps,
Mike