Database design, preferably with checkboxes

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.

On Sat, 19 Jul 2008 19:09:00 -0700, SuzyQ77
wrote:

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.

I am not new to access, but this kind of thing is still a bit above me.

Are there shifts involved? Whoever helps you may need to know that.

You already have an Employees table. Now you need a Calendar table to supply
the dates. Maybe just import the one from he
http://www.psci.net/gramelsp/temp/Calendar.zip

Now you need a EmployeeSchedules table. Maybe like so:

CREATE TABLE EmployeeSchedules (
employee_id LONG NOT NULL
References Employees (employee_id),
calendar_date DATETIME NOT NULL
References Calendar (calendar_date),
work_date YESNO DEFAULT 0 NOT NULL,
PRIMARY KEY (employee_id, calendar_date));

Now you need to fill it for this week:

INSERT INTO EmployeeSchedules
(employee_id,
calendar_date)
SELECT Employees.employee_id,
Calendar.calendar_date
FROM Calendar,
Employees
WHERE Calendar.calendar_date BETWEEN
DATEADD("d",1 - WEEKDAY(DATE()),DATE())
AND DATEADD("d",7 - WEEKDAY(DATE()),DATE());

At this point you have schedule slot for each employee for each day. You can
now mark work_date True for the days an employee will work. You will be using a
form for this, so you will need a query.

CREATE TABLE EmployeeSchedules (
employee_id LONG NOT NULL
References Employees (employee_id),
calendar_date DATETIME NOT NULL
References Calendar (calendar_date),
work_date YESNO DEFAULT 0 NOT NULL,
PRIMARY KEY (employee_id, calendar_date));

The form might have a calendar control in the heading to pick the date to work
with, i.e., to schedule. There are any numbers of ways to manage this form.

Just to show who is working this week, you might want a crosstab query:

TRANSFORM First([WillWork]) AS FirstOfwork_date
SELECT employee_name
FROM (SELECT Employees.employee_id,
Employees.employee_name,
Calendar.weekday_name,
EmployeeSchedules.calendar_date,
IIF(EmployeeSchedules.work_date = -1,"X","") AS WillWork
FROM (Employees
INNER JOIN EmployeeSchedules
ON Employees.employee_id = EmployeeSchedules.employee_id)
INNER JOIN Calendar
ON EmployeeSchedules.calendar_date = Calendar.calendar_date) AS T
GROUP BY employee_name
PIVOT weekday_name
IN("Sunday","Monday","Tuesday","Wednesday","Thursd ay","Friday","Saturday");

employee_name Sunday Monday Tuesday Wednesday Thursday Friday
Saturday
Anna X X X X
Larry X X X X
Marie X X X
Mike X X X
Susan X X X X X

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.

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.

I seem to remember posts from someone setting up this sort of thing in
access.
A search of the newsgroup might reveal how someone else was able to set this
up and get it to work.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"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.

On Mon, 21 Jul 2008 13:06:00 -0700, SuzyQ77
wrote:

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.




I had never thought about anything like this until you asked the question, so I
do not know whether I am on the right track. At any rate, it was an interesting
exercise for me.
http://www.psci.net/gramelsp/temp/Si...Scheduling.zip

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.

On Tue, 22 Jul 2008 16:20:09 +1200, "Graham Mandeno"
wrote:


Very nice! And works exactly as you explained.

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

"Michael Gramelspacher" wrote in message
...
On Tue, 22 Jul 2008 16:20:09 +1200, "Graham Mandeno"
wrote:


Very nice! And works exactly as you explained.

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.