Database design, preferably with checkboxes

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" <SuzyQ77.RemoveThis@discussions.microsoft.com> wrote in message
> news:25103A18-FF91-44A0-A122-23BB3A3807D0@microsoft.com...
> > 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" <SuzyQ77.RemoveThis@discussions.microsoft.com> wrote in message
> >> news:C0EA8911-63FD-4E4C-A7E5-2C291906D829@microsoft.com...
> >> >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.
> >>
> >>
> >>
>
>

Thanks, Michael
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

"Michael Gramelspacher" <gramelsp.RemoveThis@psci.net> wrote in message
news:shjb8499esv2p3cblall60gpmf8lb5gnnq@4ax.com...
> On Tue, 22 Jul 2008 16:20:09 +1200, "Graham Mandeno"
> <Graham.Mandeno.RemoveThis@nomail.please> wrote:
>
>
> Very nice! And works exactly as you explained.