John said:
You're trying to keep it "as simple as possible" by emulating Excel... but
that will in fact make it MUCH MORE COMPLEX.
Excel is a spreadsheet. Access is a relational database environment. *They are
very different*.
Good design for a spreadsheet is almost by definition inappropriate design for
a relational database! In particular, having a different table field for each
day is *simply wrong*, and will make your database much harder to implement
and use.
Stop, step back, and work *with* Access as it was designed (a relational
database using tall-thin normalized tables) rather than forcing it into a
Procrustean bed emulating a spreadsheet.
The words of a WISE man!! Could not have said this better if I read it from
a book! Duplicating what is In Excel is often a problem and Mr Vinson is
kind in saying that it will make "it more complex."
Here is what we did to help solve a similar issue. This may not be the best
way because there are probably many ways to accomplish this, but we know it
works. OUr way does not allow a worker to start in one day and work through
midnight into the next day. We wrote this for a retail operation that opens
8am and closes 6 or so and thus we tailored our solution to the requirement.
We have a temp table that fills and holds the schedule that is being created.
The fields are:
listfields "tbl_temp_Schedule"
!Schedule_ID (autonum)
!Employee_ID (numb)
!1Reason_ID (numb)
!1Begin_DT (DT)
!1End_DT (DT)
!2Reason_ID
!3Reason_ID
!4Reason_ID
!5Reason_ID
!6Reason_ID
!7Reason_ID
!2Begin_DT
!3Begin_DT
!4Begin_DT
!5Begin_DT
!6Begin_DT
!7Begin_DT
!2End_DT
!3End_DT
!4End_DT
!5End_DT
!6End_DT
!7End_DT
listfields "tbl_Appointment_Status"
!Status_ID
!Appointment_Type_ID
!Status
!Fore_color
!Back_Color
!Border_Color
!Notify
!Notify_minutes
!Prompt_Invoice
!Appearance
!Status_Graphic
!Status_Background_ID
!Available
!sys
'We display the colors of the various status on screen in the colors that the
user chooses... you could skip a bunch of the fields below. The notify stuff
is checked by a splash form timer that presents the user with a prompt when
the notify (t/f) is met by the Notify_mins... again... not necessary for you
probably
listfields "tbl_Appointment_Type"
!Appointment_Type_ID
!Appointment_Type (We use this for various TYPES of appointments which
employee schedule is one, dispatch, meeting, convsersation, delivery) User
can not change these
!sys (This is a field that we use so that records are not erased when create
a new "blank" db for a new customer. Have a routine that goes to each table
and only deleted records that are sys=false, thus you probably would not need
the "sys" field.
There is a bound form with fields in the detail area with the employee name
and then basically laid out with 7 columns of three rows consisting of the
reason and begin and end_DT and the record source is a qry which match the
temp_sch table above (not really rows and column, but controls [combo and
text] in a column/row looking format)
SELECT tbl_Contact.Contact_Name, tbl_temp_Schedule.*
FROM (tbl_temp_Schedule INNER JOIN tbl_Contact ON tbl_temp_Schedule.
Employee_ID = tbl_Contact.Contact_ID) INNER JOIN tbl_Contact_Type_Employee ON
tbl_Contact.Contact_ID = tbl_Contact_Type_Employee.Contact_ID
ORDER BY tbl_Contact.Contact_Name;
the column / row are 1 to 7 respectively like this
1Reason_ID
1Begin_DT
1End_DT
The reason_ID field is a Combo Box which has a rowsource that is a query of a
STATUS table
SELECT tbl_Appointment_Status.Status_ID, tbl_Appointment_Status.Status
FROM tbl_Appointment_Status
WHERE (((tbl_Appointment_Status.Appointment_Type_ID)=4));
Type_ID=4 just happens to be our Employee Schedule type. Delivery is 1,
dispatch is 2 and so on.
Once the temp sched is filled with values and confirmed, the user can then
click a command button which adds the records, overwritting any previous
"date" in the employees records. We basically run the schedule based on the
Sunday of the week the data falls in, which allows people to create a
schedule from Tuesday to the following Monday or any other date range.
code to add is this
rsttmp.Open "tbl_temp_schedule", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
Dim rstSched As New ADODB.Recordset
DoCmd.RunSQL "DELETE * FROM tbl_contact_type_Employee_schedule WHERE
Datevalue(Begin_DT)>=#" & DT & "# AND Datevalue(Begin_DT)<#" & DateAdd("ww",
1, DT) & "# AND MarketID=" & Me.cmbMarket.Column(0)
rstSched.Open "tbl_contact_type_Employee_schedule", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic
Do Until rsttmp.eof
For i = 1 To 7
If Len(rsttmp(i & "Begin_DT") & "") > 0 Or rsttmp(i & "Reason_ID") =
3 Or rsttmp(i & "Reason_ID") = 4 Then
rstSched.AddNew
rstSched!Employee_ID = rsttmp!Employee_ID
rstSched!Status_ID = rsttmp(i & "Reason_ID")
rstSched!Begin_DT = Nz(rsttmp(i & "Begin_DT"), 0)
rstSched!End_DT = Nz(rsttmp(i & "End_DT"), 0)
rstSched!DayOfWeek = WeekDay(rstSched!Begin_DT, vbSunday)
rstSched!ScheduleSunday = DateValue(DateAdd("d", -(rstSched!
DayOfWeek - 1), rstSched!Begin_DT))
rstSched!MarketID = Me.cmbMarket.Column(0)
If Not rstUpdate(rstSched) Then GoTo exithere
End If
Next i
rsttmp.MoveNext
Loop
this deletes previous set times in the data and fills in the new information.
Again, we found doing it this way allows the user to create a schedule for
Monday through the next Sunday (7 days) and then open the form back up and
select Thrs through Weds and have the values that were already entered appear
and store the new ones, plus it allows printing any 7 day work schedule, no
matter which starting day they pick.
Just wanted to give you a taste of what you might be in for. As one clever
person already pointed out he would rather write a program that takes in agg
prod by weight and sends it out by size... I concur!!!
Hope the above is not too much, but some assistance in how you might be able
to attact your problem.
