Select multiple text values and display in one field

[Keenan]

We are trying to design and basic database that acts as a timetable templete
for departments to prepare their school timetables and will ensure consistant
data and formatting to reduce GIGO for easier inputting into the full school
timetable system.

We have all the basic information and numerous tables will lookups etc, but
have now hit a brick wall because we need an easier way to enter the week/s
each module/course is running rather than just trying in which can create
erros. ie.

Fields: Day - Start time - End time - Course - Module - Teacher - Room - Weeks
Record: Thu - 08:00AM - 12:00PM - ABC - ABC01 - MR. SMITH - 101 - ??????

We want coordinators to be able enter the weeks the course is running via a
list or popup box ie.
Week - Date (Monday) - Select
14 - 30/3/09 - (tick box)
15 - 6/4/09 - (tick box)
etc, etc.

From those weeks that are selected we want it to be listed in the weeks
field in the first table ie.
Fields: Day - Start time - End time - Course - Module - Teacher - Room - Weeks
Record: Thu - 08:00AM - 12:00PM - ABC - ABC01 - MR. SMITH - 101 - 14,15,16,19

Hope this is possible. Thanks

 

[Tom van Stiphout]

On Thu, 30 Apr 2009 20:44:01 -0700, Keenan

Not the db design I would choose, but here goes:
You could have an unbound section on your form with 52 checkboxes for
all weeks in the year. Let's call them chkWeek1 through chkWeek52. You
also add a button: Save Weeks. In its click event you loop over the
controls, concatenating the results string. Something like:
dim strWeeks as string
dim i as integer
for i = 1 to 52
if Me.Controls("chkWeek" & i) = True then strWeeks = strWeeks & i &
","
next i
if len(strWeeks)>0 then strWeeks = Left$(strWeeks, len(strWeeks)-1)
'Strip trailing comma
Me.myWeeksField.Value = strWeeks 'Save to the bound field.

-Tom.
Microsoft Access MVP

 

[Graham Mandeno]

Hi Keenan

WARNING: There is a lot to digest in this message, so please persevere to
the end

If you store the weeks as a string then you will have a devil of a job
detecting clashes, which I would think was of paramount importance for a
timetabling system.

For example, the following two records clearly clash in week 15:

Thu - 08:00AM - 12:00PM - ABC - ABC01 - MR. SMITH - 101 - 14,15,16,19
Thu - 11:00AM - 01:00PM - XYZ - XYZ09 - MRS JONES - 101 - 13,15,17

But how can you write code to detect this condition?

Also, would you not often have a situation where the same class is in the
same room for the same time slot several days of the week - say Mon, Wed,
Fri?

I would remove the day and week from the table and add an ID field, then add
two more tables for BookingDays (ID and DayNumber) and BookingWeeks (ID and
WeekNumber).

Also, assuming each class is always taught by the same teacher, remove the
Course, Module and Teacher into a separate Classes table.

This leaves you with:

Classes:
ClassID (autonumber)
Course
Module
Teacher

Bookings:
BookingID (autonumber)
Room
StartTime
EndTime
ClassID

BookingDays:
BookingID
DayNumber

BookingWeeks:
BookingID
WeekNumber

Days:
DayNumber (1=Monday,2=Tuesday,etc)
DayName

Weeks:
WeekNumber
WeekDate (date of Monday)

Just to clarify, your example class booked for Mon-Wed-Fri in weeks
14,15,16,19 would have the following records:

Classes:
123, ABC, ABC01, MR. SMITH

Bookings:
99, 101, 08:00AM, 12:00PM, 123

BookingDays:
99, 1
99, 3
99, 5

BookingWeeks:
99, 14
99, 15
99, 16
99, 19

This gives you the flexibility to build all sorts of queries - for example,
the following query will list all the starting and ending dates *and* times
and room numbers for every session of a given class:

SELECT Bookings.ClassID,
Bookings.Room,
[WeekDate]+[Days].[DayNumber]-1+[StartTime] AS StartDateTime,
[WeekDate]+[Days].[DayNumber]-1+[EndTime] AS EndDateTime
FROM (((Bookings INNER JOIN BookingDays
ON Bookings.BookingID = BookingDays.BookingID)
INNER JOIN BookingWeeks
ON Bookings.BookingID = BookingWeeks.BookingID)
INNER JOIN Days ON BookingDays.DayNumber = Days.DayNumber)
INNER JOIN Weeks ON BookingWeeks.WeekNumber = Weeks.WeekNumber
WHERE (Bookings.ClassID=123);

Now, the problem is creating a user interface that makes it easy to append
or delete records in your BookingDays and BookingWeeks tables simply by
ticking/unticking checkboxes.

The trick is to create a query that lists ALL the days (or Weeks) with a
calculated yes/no field to indicate if the corresponding BookingDays (or
BookingWeeks) record is present for the given BookingID:

SELECT Days.DayName, Days.DayNumber,
[Days].[DayNumber] In (Select DayNumber from BookingDays
where BookingID=Nz(Forms!frmBookings!BookingID,0)) AS Selected
FROM BookingDays RIGHT JOIN Days
ON BookingDays.DayNumber = Days.DayNumber;

This query will list all the days with their numbers, and a third column
(Selected) will show show True (-1) or False (0) depending on whether a
BookingDays record exists for the given day and the BookingID from the form
frmBookings.

You could use this query as the RecordSource of a popup "Day Selector" form
with a textbox bound to DayName and a checkbox bound to Selected. Both
these controls should be disabled and locked.

Now, the coup de grace...

You place a transparent command button over the checkbox named cmdToggle.
This command button either creates or deletes a record as required, and
requeries the form:

Private Sub cmdToggle_Click()
Dim sSql As String
On Error GoTo ProcErr
If Me.Selected Then
sSql = "Delete from BookingDays " _
& " where BookingID=Forms!frmBookings!BookingID " _
& " and DayNumber=" & Me.DayNumber
Else
sSql = "Insert into BookingDays (BookingID, DayNumber) " _
& "values (Forms!frmBookings!BookingID, " _
& Me.DayNumber & ")"
End If
CurrentDb.Execute sSql, dbFailOnError
Me.Requery
ProcEnd:
On Error Resume Next
Exit Sub
ProcErr:
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume ProcEnd
End Sub

Finally, you can write two functions to return comma separated lists of days
(or weeks) given the BookingID passed as an argument. These generated
strings cannot be edited, of course, but they can be used to give a simple
display of the days and weeks that a room is booked.

I know there is a lot of information to digest here, particularly if you are
a beginner, but I think it will be worthwhile considering this approach to
avoid insurmountable problems further down the track.

 

[Graham Mandeno]

Hi Keenan

I have posted about three times in the last few days, but I can't see them
in the newsreader. Please forgive me is a whole lot of duplicates turn up.

WARNING: There is a lot to digest in this message, so please persevere to
the end

If you store the weeks as a string then you will have a devil of a job
detecting clashes, which I would think was of paramount importance for a
timetabling system.

For example, the following two records clearly clash in week 15:

Thu - 08:00AM - 12:00PM - ABC - ABC01 - MR. SMITH - 101 - 14,15,16,19
Thu - 11:00AM - 01:00PM - XYZ - XYZ09 - MRS JONES - 101 - 13,15,17

But how can you write code to detect this condition?

Also, would you not often have a situation where the same class is in the
same room for the same time slot several days of the week - say Mon, Wed,
Fri?

I would remove the day and week from the table and add an ID field, then add
two more tables for BookingDays (ID and DayNumber) and BookingWeeks (ID and
WeekNumber).

Also, assuming each class is always taught by the same teacher, remove the
Course, Module and Teacher into a separate Classes table.

This leaves you with:

Classes:
ClassID (autonumber)
Course
Module
Teacher

Bookings:
BookingID (autonumber)
Room
StartTime
EndTime
ClassID

BookingDays:
BookingID
DayNumber

BookingWeeks:
BookingID
WeekNumber

Days:
DayNumber (1=Monday,2=Tuesday,etc)
DayName

Weeks:
WeekNumber
WeekDate (date of Monday)

Just to clarify, your example class booked for Mon-Wed-Fri in weeks
14,15,16,19 would have the following records:

Classes:
123, ABC, ABC01, MR. SMITH

Bookings:
99, 101, 08:00AM, 12:00PM, 123

BookingDays:
99, 1
99, 3
99, 5

BookingWeeks:
99, 14
99, 15
99, 16
99, 19

This gives you the flexibility to build all sorts of queries - for example,
the following query will list all the starting and ending dates *and* times
and room numbers for every session of a given class:

SELECT Bookings.ClassID,
Bookings.Room,
[WeekDate]+[Days].[DayNumber]-1+[StartTime] AS StartDateTime,
[WeekDate]+[Days].[DayNumber]-1+[EndTime] AS EndDateTime
FROM (((Bookings INNER JOIN BookingDays
ON Bookings.BookingID = BookingDays.BookingID)
INNER JOIN BookingWeeks
ON Bookings.BookingID = BookingWeeks.BookingID)
INNER JOIN Days ON BookingDays.DayNumber = Days.DayNumber)
INNER JOIN Weeks ON BookingWeeks.WeekNumber = Weeks.WeekNumber
WHERE (Bookings.ClassID=123);

Now, the problem is creating a user interface that makes it easy to append
or delete records in your BookingDays and BookingWeeks tables simply by
ticking/unticking checkboxes.

The trick is to create a query that lists ALL the days (or Weeks) with a
calculated yes/no field to indicate if the corresponding BookingDays (or
BookingWeeks) record is present for the given BookingID:

SELECT Days.DayName, Days.DayNumber,
[Days].[DayNumber] In (Select DayNumber from BookingDays
where BookingID=Nz(Forms!frmBookings!BookingID,0)) AS Selected
FROM BookingDays RIGHT JOIN Days
ON BookingDays.DayNumber = Days.DayNumber;

This query will list all the days with their numbers, and a third column
(Selected) will show show True (-1) or False (0) depending on whether a
BookingDays record exists for the given day and the BookingID from the form
frmBookings.

You could use this query as the RecordSource of a popup "Day Selector" form
with a textbox bound to DayName and a checkbox bound to Selected. Both
these controls should be disabled and locked.

Now, the coup de grace...

You place a transparent command button over the checkbox named cmdToggle.
This command button either creates or deletes a record as required, and
requeries the form:

Private Sub cmdToggle_Click()
Dim sSql As String
On Error GoTo ProcErr
If Me.Selected Then
sSql = "Delete from BookingDays " _
& " where BookingID=Forms!frmBookings!BookingID " _
& " and DayNumber=" & Me.DayNumber
Else
sSql = "Insert into BookingDays (BookingID, DayNumber) " _
& "values (Forms!frmBookings!BookingID, " _
& Me.DayNumber & ")"
End If
CurrentDb.Execute sSql, dbFailOnError
Me.Requery
ProcEnd:
On Error Resume Next
Exit Sub
ProcErr:
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume ProcEnd
End Sub

Finally, you can write two functions to return comma separated lists of days
(or weeks) given the BookingID passed as an argument. These generated
strings cannot be edited, of course, but they can be used to give a simple
display of the days and weeks that a room is booked.

I know there is a lot of information to digest here, particularly if you are
a beginner, but I think it will be worthwhile considering this approach to
avoid insurmountable problems further down the track.