Database design, preferably with checkboxes

External Since: Nov 03, 2007 Posts: 5

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.

External Since: Apr 03, 2006 Posts: 238

On Sat, 19 Jul 2008 19:09:00 -0700, SuzyQ77 <SuzyQ77.RemoveThis@discussions.microsoft.com>
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 here:
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","Thursday","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