Time Schedule Query

[Debbie D.]

Time Schedule Query

I have database, the purpose of which is for students to book a date and
time for a test. Tests can only be taken at the following days/times and
last 45mins:
Mondays: 11:30 to 12:30 and 12:30 to 13:30
Thursdays: 17:00 to 18:00, 18:00 to 19:00 and 19:00 to 20:00

There are 12 computers which can be booked. As a further compaction all of
these PC can either perform a full test or a primer version of the test. A
form takes the bookings and stores them in a table named: TestAppointments
the structure of which is:

[TestAppointments]
ApptDate, (Date/Time) (composite primary)
AppTime (Number Lg Int) ?? (composite primary)
TestMode (Text size 10 composite primary) coming from a combo on a form for
either Test/Primer)
WorkStationID (Number Lg Int) (composite primary)
StudentID (Number Lg Int) related through a students table storing relevant
info, the many side of a 1-m. Also indexed unique)

The above table will not allow a student to mistakably be booked on either a
test or primer at the same time/date slot.

Through kind information provided by this board the following was then
undertaken.

The above table only stores booked information regarding dates, times, PC
and a test or primer version etc. and through a combo box the fact whether
the PC and its time slot is being used for either a Test or Primer. This
table then generates a reporting giving full daily details of the usage of
the room.

The problem was after this to create a query that would list any vacant time
slots for future booking details with the available time slots and
WorkStationIDs. Another table was introduced that details a full 12 months
of availability as follows:

[YearlySchedule]
Date (Date/Time composite key)
WorkStationID (Number Lg Int composite key)
TestMode (text size 10 (test or primer composite key)
TestTime (Date/Time composite key)
StudentID (Num Lg Int not used not related??)

Something that just bugs me, shouldn’t I be relating the
[TestAppointments.StudentID] > [YearlySchedule.StudentID] if so how? All
these composite primary keys give me interminate when trying to join. And
will I now nearly to change the type of the [TestAppointments.ApptTime] to
match the YearlySchedule.TestTime] for the following query to work
correctly?? In the YearlySchedule table there are now correct 4008 available
testing/primer time slots available.

A kind member then assisted me with the following SQL:

SELECT YearlySchedule.Date, YearlySchedule.TestTime,
YearlySchedule.WorkStationID, TestAppointments.ApptDate
FROM YearlySchedule LEFT JOIN TestAppointments ON YearlySchedule.Date =
TestAppointments.ApptDate
WHERE (((TestAppointments.ApptDate) Is Null));

And it worked perfectly when querying an empty TestAppointements table.
However if I put just one single appointment in there i.e. 12 Sept 2005
(which should approximately 59 free that day) the number drops to some like
3948 which is taking all the available day slots for just one time slot and
then continues showing all the next days henceforth as free.


Soo sorry to dragging this on but I’ve been 4 days at this keyboard. Many
thanks for taking the time to read this, any help appreciated.

Debbie D.

 

[Eric D via AccessMonster.com]

Debbie,

I don't want to advertise my email address here, so, if you give me yours,
I'll contact you. If you send me a copy of what you have, I'll help you out.
It sounds like you need to modify your query to list time slots that are
available within a specific date frame. You can prompt for this on your form
and then pass it to the query.

Again, if you want me to take a look at what you have, let me know.

Time Schedule Query

I have database, the purpose of which is for students to book a date and
time for a test. Tests can only be taken at the following days/times and
last 45mins:
Mondays: 11:30 to 12:30 and 12:30 to 13:30
Thursdays: 17:00 to 18:00, 18:00 to 19:00 and 19:00 to 20:00

There are 12 computers which can be booked. As a further compaction all of
these PC can either perform a full test or a primer version of the test. A
form takes the bookings and stores them in a table named: TestAppointments
the structure of which is:

[TestAppointments]
ApptDate, (Date/Time) (composite primary)
AppTime (Number Lg Int) ?? (composite primary)
TestMode (Text size 10 composite primary) coming from a combo on a form for
either Test/Primer)
WorkStationID (Number Lg Int) (composite primary)
StudentID (Number Lg Int) related through a students table storing relevant
info, the many side of a 1-m. Also indexed unique)

The above table will not allow a student to mistakably be booked on either a
test or primer at the same time/date slot.

Through kind information provided by this board the following was then
undertaken.

The above table only stores booked information regarding dates, times, PC
and a test or primer version etc. and through a combo box the fact whether
the PC and its time slot is being used for either a Test or Primer. This
table then generates a reporting giving full daily details of the usage of
the room.

The problem was after this to create a query that would list any vacant time
slots for future booking details with the available time slots and
WorkStationIDs. Another table was introduced that details a full 12 months
of availability as follows:

[YearlySchedule]
Date (Date/Time composite key)
WorkStationID (Number Lg Int composite key)
TestMode (text size 10 (test or primer composite key)
TestTime (Date/Time composite key)
StudentID (Num Lg Int not used not related??)

Something that just bugs me, shouldn’t I be relating the
[TestAppointments.StudentID] > [YearlySchedule.StudentID] if so how? All
these composite primary keys give me interminate when trying to join. And
will I now nearly to change the type of the [TestAppointments.ApptTime] to
match the YearlySchedule.TestTime] for the following query to work
correctly?? In the YearlySchedule table there are now correct 4008 available
testing/primer time slots available.

A kind member then assisted me with the following SQL:

SELECT YearlySchedule.Date, YearlySchedule.TestTime,
YearlySchedule.WorkStationID, TestAppointments.ApptDate
FROM YearlySchedule LEFT JOIN TestAppointments ON YearlySchedule.Date =
TestAppointments.ApptDate
WHERE (((TestAppointments.ApptDate) Is Null));

And it worked perfectly when querying an empty TestAppointements table.
However if I put just one single appointment in there i.e. 12 Sept 2005
(which should approximately 59 free that day) the number drops to some like
3948 which is taking all the available day slots for just one time slot and
then continues showing all the next days henceforth as free.

Soo sorry to dragging this on but I’ve been 4 days at this keyboard. Many
thanks for taking the time to read this, any help appreciated.

Debbie D.

 

[Debbie D.]

Eric,

This is very kind of you indeed and unexpected. I'm enjoying the chellenge
but after 5 days starting to hit the wall. Hopefully I have same some effort
on your part and uploaded the database name 64.mdb to the following web site:

http://www.blackburncollege.plus.com/access/
or directly
http://www.blackburncollege.plus.com/access/64.mdb

Many thanks

Debs (UK)

Debbie,

I don't want to advertise my email address here, so, if you give me yours,
I'll contact you. If you send me a copy of what you have, I'll help you out.
It sounds like you need to modify your query to list time slots that are
available within a specific date frame. You can prompt for this on your form
and then pass it to the query.

Again, if you want me to take a look at what you have, let me know.

Time Schedule Query

I have database, the purpose of which is for students to book a date and
time for a test. Tests can only be taken at the following days/times and
last 45mins:
Mondays: 11:30 to 12:30 and 12:30 to 13:30
Thursdays: 17:00 to 18:00, 18:00 to 19:00 and 19:00 to 20:00

There are 12 computers which can be booked. As a further compaction all of
these PC can either perform a full test or a primer version of the test. A
form takes the bookings and stores them in a table named: TestAppointments
the structure of which is:

[TestAppointments]
ApptDate, (Date/Time) (composite primary)
AppTime (Number Lg Int) ?? (composite primary)
TestMode (Text size 10 composite primary) coming from a combo on a form for
either Test/Primer)
WorkStationID (Number Lg Int) (composite primary)
StudentID (Number Lg Int) related through a students table storing relevant
info, the many side of a 1-m. Also indexed unique)

The above table will not allow a student to mistakably be booked on either a
test or primer at the same time/date slot.

Through kind information provided by this board the following was then
undertaken.

The above table only stores booked information regarding dates, times, PC
and a test or primer version etc. and through a combo box the fact whether
the PC and its time slot is being used for either a Test or Primer. This
table then generates a reporting giving full daily details of the usage of
the room.

The problem was after this to create a query that would list any vacant time
slots for future booking details with the available time slots and
WorkStationIDs. Another table was introduced that details a full 12 months
of availability as follows:

[YearlySchedule]
Date (Date/Time composite key)
WorkStationID (Number Lg Int composite key)
TestMode (text size 10 (test or primer composite key)
TestTime (Date/Time composite key)
StudentID (Num Lg Int not used not related??)

Something that just bugs me, shouldn’t I be relating the
[TestAppointments.StudentID] > [YearlySchedule.StudentID] if so how? All
these composite primary keys give me interminate when trying to join. And
will I now nearly to change the type of the [TestAppointments.ApptTime] to
match the YearlySchedule.TestTime] for the following query to work
correctly?? In the YearlySchedule table there are now correct 4008 available
testing/primer time slots available.

A kind member then assisted me with the following SQL:

SELECT YearlySchedule.Date, YearlySchedule.TestTime,
YearlySchedule.WorkStationID, TestAppointments.ApptDate
FROM YearlySchedule LEFT JOIN TestAppointments ON YearlySchedule.Date =
TestAppointments.ApptDate
WHERE (((TestAppointments.ApptDate) Is Null));

And it worked perfectly when querying an empty TestAppointements table.
However if I put just one single appointment in there i.e. 12 Sept 2005
(which should approximately 59 free that day) the number drops to some like
3948 which is taking all the available day slots for just one time slot and
then continues showing all the next days henceforth as free.

Soo sorry to dragging this on but I’ve been 4 days at this keyboard. Many
thanks for taking the time to read this, any help appreciated.

Debbie D.

 

[Eric D via AccessMonster.com]

Debbie,

How far in advance do you want to schedule tests?

Eric,

This is very kind of you indeed and unexpected. I'm enjoying the chellenge
but after 5 days starting to hit the wall. Hopefully I have same some effort
on your part and uploaded the database name 64.mdb to the following web site:

http://www.blackburncollege.plus.com/access/
or directly
http://www.blackburncollege.plus.com/access/64.mdb

Many thanks

Debs (UK)

Debbie,

[quoted text clipped - 78 lines]

 

[Debbie D.]

Eric,

Thanks again for you kind response. Testing is allowed no more than 3 weeks
in advanced. Students tend to forget and then not turn up for an
appointment. There has to be a teacher in the room. Let me know if there is
more info required. What a STAR!! Thanks again.

Debbie D.

Debbie,

How far in advance do you want to schedule tests?

Eric,

This is very kind of you indeed and unexpected. I'm enjoying the chellenge
but after 5 days starting to hit the wall. Hopefully I have same some effort
on your part and uploaded the database name 64.mdb to the following web site:

http://www.blackburncollege.plus.com/access/
or directly
http://www.blackburncollege.plus.com/access/64.mdb

Many thanks

Debs (UK)

Debbie,

[quoted text clipped - 78 lines]

Debbie D.

 

[Eric D via AccessMonster.com]

Debbie,

I'm looking at the YearlySchedule and have questions:

When a student requests a Timeframe to use one of the available computers, do
they indicate, at that time, that they want to take a test or use the
computer as a primer?

I notice that you have Testmode as part of your primary key for this table.
Unless I misunderstand the use of this field, I think this is part of your
problem. The way it is set up now, you have two unique records for every
workstation in this table - one as a Test machine and one with the same
Date/Time as a Primer machine. Even though your Student is entered into this
table as having that machine for a specific Date/Time, for a Test or as a
Primer, you need to update BOTH records with the assigned time if you want to
eliminate that from specific timeframe from your query of what is available.

I ran a query to delete all records in the YearlySchedule table that had
Primer in the Testmode field.
This leaves 2856 records.
I modified this table, setting the primary key to Date/Time/WorkStationID.
This provides one unique record for every time slot on every workstation
listed.
I modified the SQL query, deleting the TestAppointments table from this query.

I added >=Now() to the criteria for Date
I added is null to the criteria for StudentID.
My results listed 2722 records.

Based on this, if it's not too much work, I would do the following:
I would make the changes just mentioned plus,
I would not use the TestAppointments table.
I would modify the SQL query to filter dates that are not available... ie.,
past dates not assigned or dates that fall into the next quarter, etc.

When a student is assigned a time slot, the update process would update
YearlySchedule and make note as to whether or not the assignment was for a
Test or as a Primer.

If I were working from scratch on this, my YearlySchedule would be a simple
lookup table that contained: Date, Time, WorkstationID. Nothing more. All
three would combine to make the primary key.
I would create an Appointment table that would keep assigned time slots
seperate. I would add the TestMode and StudentID to this table.

This is personal preference. I don't think your database will be that large
that performance would suffer whether you do it one way or the other. As I
see it, each year, you will end up deleting last years records as they would
serve no purpose (that I can think of).

Please let me know if I can be of any further help.

Eric,

This is very kind of you indeed and unexpected. I'm enjoying the chellenge
but after 5 days starting to hit the wall. Hopefully I have same some effort
on your part and uploaded the database name 64.mdb to the following web site:

http://www.blackburncollege.plus.com/access/
or directly
http://www.blackburncollege.plus.com/access/64.mdb

Many thanks

Debs (UK)

Debbie,

[quoted text clipped - 78 lines]

 

[Debbie D.]

Eric,

It is usual that a student wish to take a test first say 17:00 and then go
back into the room at 18:00 and take a full test. However on the first
sitting you can just take a test. Some students just book a primer. However
the majority are in the first case, book primer, then test.

Thanks as always.

D. D.

Debbie,

I'm looking at the YearlySchedule and have questions:

When a student requests a Timeframe to use one of the available computers, do
they indicate, at that time, that they want to take a test or use the
computer as a primer?

I notice that you have Testmode as part of your primary key for this table.
Unless I misunderstand the use of this field, I think this is part of your
problem. The way it is set up now, you have two unique records for every
workstation in this table - one as a Test machine and one with the same
Date/Time as a Primer machine. Even though your Student is entered into this
table as having that machine for a specific Date/Time, for a Test or as a
Primer, you need to update BOTH records with the assigned time if you want to
eliminate that from specific timeframe from your query of what is available.

I ran a query to delete all records in the YearlySchedule table that had
Primer in the Testmode field.
This leaves 2856 records.
I modified this table, setting the primary key to Date/Time/WorkStationID.
This provides one unique record for every time slot on every workstation
listed.
I modified the SQL query, deleting the TestAppointments table from this query.

I added >=Now() to the criteria for Date
I added is null to the criteria for StudentID.
My results listed 2722 records.

Based on this, if it's not too much work, I would do the following:
I would make the changes just mentioned plus,
I would not use the TestAppointments table.
I would modify the SQL query to filter dates that are not available... ie.,
past dates not assigned or dates that fall into the next quarter, etc.

When a student is assigned a time slot, the update process would update
YearlySchedule and make note as to whether or not the assignment was for a
Test or as a Primer.

If I were working from scratch on this, my YearlySchedule would be a simple
lookup table that contained: Date, Time, WorkstationID. Nothing more. All
three would combine to make the primary key.
I would create an Appointment table that would keep assigned time slots
seperate. I would add the TestMode and StudentID to this table.

This is personal preference. I don't think your database will be that large
that performance would suffer whether you do it one way or the other. As I
see it, each year, you will end up deleting last years records as they would
serve no purpose (that I can think of).

Please let me know if I can be of any further help.

Eric,

This is very kind of you indeed and unexpected. I'm enjoying the chellenge
but after 5 days starting to hit the wall. Hopefully I have same some effort
on your part and uploaded the database name 64.mdb to the following web site:

http://www.blackburncollege.plus.com/access/
or directly
http://www.blackburncollege.plus.com/access/64.mdb

Many thanks

Debs (UK)

Debbie,

[quoted text clipped - 78 lines]

Debbie D.