Multiple subforms on one form

[Johnny]

I am creating an overtinme sign up sheet. So I will have multiple dates each that has its own signup table. The signup table is a sub to the available overtime date.

Problem is I can only show one day at a time, is there a way to show all the available days of overtime with a subform signup table below each one?

It is easier to see what you're signing up for when you can see dates in a row on one page. I am open to a new approach though if I need to start over with my design.

So it would look like on one page:
Dayofovertime
-- signup table
Dayofovertime
--signup table
Dayofovertime
--signup table
etc

DB included in case it helps to clarify what I mean.

 

[JamesMcS]

Hi! You probably shouldn't have a separate table for each day of overtime, that'll cause you problems down the line, as well as not being the way to keep the DB normalised.

Can you not just have a date field in your overtime assignment table?

 

[CBrighton]

Hi! You probably shouldn't have a separate table for each day of overtime, that'll cause you problems down the line, as well as not being the way to keep the DB normalised.

Can you not just have a date field in your overtime assignment table?

I agree.

One interface which would work with a single table system would be to have a listbox with all available dates on one side of the screen and a command button (and any other controls to capture additional data) which will book overtime on the date selected in the listbox.

You could take this further by having additional events. E.g. you could have a subform detailing who is off on the selected day. Selecting a date in the listbox could requery the subform.

This would mean you see all dates, see what's already booked and book overtime on any date all within a single form (plus it's subform).

 

[Johnny]

I'm sorry to be dense but I don't quite get what you mean for the new design?

If the signup slots are all on the Date of overtime table how do I know how many fields are needed since I won't know how many possible people will consider signging up, or have I completely missed the point.

One person could sign up for many seperate dates so I thought I had to make that a 1 to many relationship.

 

[Johnny]

I think I had a flash of what you mean, that is to have one day, but seperate the 2 hour blocks of available overtime for that one day into their own table with a signup table for each block of time but seems to have take me back around to the showing multiple signup sheets on one page issue.

 

[CBrighton]

We only know what you have told us, but by the looks of it you plan to have a table per day?

What is being suggested is changing that to a single table.

Here is an example, note I don't know what fields you have so it may not relate to you directly.

EmpID   OvertimeDate  OvertimeHours
12141    12/03/2011       07
15714    13/03/2011       10
13451    12/03/2011       03

 

[Johnny]

Okay I see what you mean, no the overtime offered is in specific 2 hour blocks rather then a set time span each day.

So it would be:

03/04/11 07-0900
--sign up table of employee's interested in this day and hour block
03/06/11 17-1900
-- sign up table etc
03/09/11 21-2300
--sign up table etc
03/09/11 21-2300
--sign up table

Each day could have mutliple 2 hour blocks available and more then one employeee can try to "win" a single block of overtime. It's not first come first serve unfortunately.

Does your design idea still work?

 

[CBrighton]

Yes, you should still use a single table rather than a table per day if you use set blocks of time.

 

[Johnny]

I have redesigned as you guys have recommended and it works MUCH better, thank you for your time and assistance!

John