Database design, preferably with checkboxes

Please post back as to how it goes. This is a frequent topic and I for one
am interested. Just not enough free time to play with it. Pete D.
"SuzyQ77" wrote in message
...
Thank you so much! I'll be working on this database tomorrow and let you
know how I did. From what Michael states in his post, it does work just
like
you said, so I'm sure if I follow your design it will work for me as well.
I'm pretty new to Access, but your directions are clear for me to follow.
Thanks again.

"Graham Mandeno" wrote:

Hi Suzy

There is a strong temptation here to compromise on the table design so as
to
make the desired user interface easy to implement. This is NEVER a good
idea.

The best table design here is two tables - Employees and WorkSchedule.

Employees holds all your employee information - names, addresses, etc and
has a numeric primary key "EmployeeID" (let's say it is an autonumber).

EmployeeID has only two fields - WorkDate (date field) and EmployeeID
(numeric). These two fields together make up a composite primary key (an
employee can't work twice on the same date) and there is a one-to many
relationship between Employees and WorkSchedule on EmployeeID.

Now it is a simple matter to list all the days an employee is working, or
to
list all the employees working on a particular day. The problem is to
create your form with an array of checkboxes to build the schedule.

We will actually base the form on a query that looks and behaves like a
crosstab query. Create a new query and paste the following code into the
SQL view window, then save the query as "frmWorkSchedule":

=================== start code =====================
PARAMETERS [Forms]![frmWorkSchedule]![txtStartOfWeek] DateTime;
SELECT Employees.EmployeeID, [LastName] & ", " & [FirstName] AS EmpName,
Exists (Select EmployeeID from WorkSchedule as W
where W.EmployeeID=Employees.EmployeeID
and WorkDate=[Forms]![frmWorkSchedule]![txtStartOfWeek]) AS Mon,
Exists (Select EmployeeID from WorkSchedule as W
where W.EmployeeID=Employees.EmployeeID
and WorkDate=[Forms]![frmWorkSchedule]![txtStartOfWeek]+1) AS Tue,
Exists (Select EmployeeID from WorkSchedule as W
where W.EmployeeID=Employees.EmployeeID
and WorkDate=[Forms]![frmWorkSchedule]![txtStartOfWeek]+2) AS Wed,
Exists (Select EmployeeID from WorkSchedule as W
where W.EmployeeID=Employees.EmployeeID
and WorkDate=[Forms]![frmWorkSchedule]![txtStartOfWeek]+3) AS Thu,
Exists (Select EmployeeID from WorkSchedule as W
where W.EmployeeID=Employees.EmployeeID
and WorkDate=[Forms]![frmWorkSchedule]![txtStartOfWeek]+4) AS Fri
FROM Employees
ORDER BY Employees.LastName, Employees.FirstName;
=================== end code =====================

Now, create a new tabular (continuous) form with qryfrmWorkSchedule as
its
RecordSource. In the detail section, place a textbox bound to EmpName
and
five checkboxes bound to Mon, Tue, Wed, Thu and Fri. Name these
checkboxes
"chkMon", "chkTue", etc. Select all these controls and set Enabled=No
and
Locked=Yes.

In the header of the form, place appropriate heading labels over each
column
(Employee, Mon, Tue,...) and add another unbound textbox named
txtStartOfWeek with a date format of your choosing. Save the form as
frmWorkSchedule.

You now have a read-only form which will show who is working on which day
of
the week starting with the date in the header textbox (assuming that date
is
a Monday). The read-only part is just a temporary problem! ;-)

Now, open the form again in design view and create five small transparent
command buttons. Name these cmdMod, cmdTue, etc and place each one over
its
corresponding checkbox. Now set the OnClick property for cmdMon to
=ToggleWork(0), and for cmdTue to =Togglework(1) etc.

Finally, paste this code into the form module:

=================== start code =====================
Option Compare Database
Option Explicit

Private Function ToggleWork(DayOffset As Integer)
Dim EmpID As Long, dt As Date, sDate As String
EmpID = Me!EmployeeID
dt = txtStartOfWeek + DayOffset
sDate = Format(dt, "\#yyyy\-mm\-dd\#")
If Me("Chk" & Format(dt, "ddd")).Value Then
CurrentDb.Execute "Delete from WorkSchedule where EmployeeID=" _
& EmpID & " and WorkDate=" & sDate
Else
CurrentDb.Execute "Insert into WorkSchedule " _
& "(EmployeeID, WorkDate) Values (" & EmpID & ", " & sDate & ")"
End If
Me.Requery
With Me.RecordsetClone
.FindFirst "EmployeeID=" & EmpID
Me.Bookmark = .Bookmark
End With
End Function

Private Sub Form_Load()
'default to next week
txtStartOfWeek = Date + 7
txtStartOfWeek_AfterUpdate
End Sub

Private Sub txtStartOfWeek_AfterUpdate()
' make sure it's a Monday
txtStartOfWeek = txtStartOfWeek - Weekday(txtStartOfWeek) + vbMonday
Me.Requery
End Sub
=================== end code =====================

Save the form and open it. Now, when you click on one of the checkboxes,
you are actually clicking on the transparent command button that overlays
it. The Click event code examines the current state of the checkbox
(does a
WorkSchedule record exist for the given date/employee or not?) and either
creates the corresponding record or deletes it. Then the form is
requeries
and repositioned back to the current employee record.

If you enter a date in the header textbox, the AfterUpdate event code
ensures that it is a Monday and then requeries the form.

When the form loads, the start date is initially set to the Monday of the
next week.

Post back and tell us how you get on.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


"SuzyQ77" wrote in message
...
No VBA or VB6. I have made simple databases in the past. Perhaps
checkboxes
is the wrong choice for this new database. I can easily print off
those
employees working on Monday, one for Tuesday, etc. by using "yes" as
the
criteria. My problem comes when I'm trying to print off the following
report. (or query)

Monday Tuesday Wednesday Thursday Friday
Jones Jones Jones
Jones
Smith
Thomas Thomas
Thomas

These schedules change from week to week. When I try to print off a
report
now, I
get their names down the side and "X's" across. I need the report to
look
like the one above. I don't need to deal with dates, just straight
Monday
through Friday. I have also tried fields in a table (and a form
created
from
the table) of Day 1, Day 2, Day 3, etc. Then in the form view, typing
in
Monday, Tuesday, Wednesday, etc. Every week I go back to the form and
delete
or clear the field on the day they won't be working. I have 65
employees,
and about 30 of them work any one day. I am using an Excel worksheet
right
now and cutting and pasting their name across the spreadsheet. I need
to
get
this on one page also, currently not a problem in Excel. This is not
the
quickest method. So I'm open to ideas! Thanks.



"Jeanette Cunningham" wrote:

Suzy,
Do you have any coding experience with either VBA or VB6?
Have you used other database applications and created tables and
queries?
Have you developed an application before?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"SuzyQ77" wrote in message
...
I need a database that will show an employees scheduled days (Monday
through
Friday). I would like to be able to just use a checkbox on a form
and
check
whether they will work that scheduled day or not for the following
week.
It
is so easy to just do a checkbox than having to type in Monday,
Tuesday,
Wednesday, etc. There are approximately 70 employees with schedules
that
change weekly! Then I can produce a query and report that will just
give
me
those employees that will work on Monday, a separate report for
Tuesday,
etc.
I also need a report that will give me a week at a glance (on one
page),
Monday through Friday and under each day heading, what employees
will
be
working. I have gotten as far as making the initial table with all
usual
employee info. I could even get my checkboxes to print out the
queries
and
reports I needed for the separate day reports by using the "Yes"
criteria
in
my query. The problem is in the week report. I just couldn't get
it
to
work, and so I've scrapped the whole checkbox thing and am starting
from
scratch. What do you suggest? I'm new to Access, so go easy on the
tech
talk.