DB Design Question

[Test User]

Hi all, (please excuse the crosspost as I'm trying to reach as many people
as possible)

I am somewhat familiar with Access 2000, but my latest project has me
stumped. So, I defer to you experts.
I've been asked to create a Daily Log sheet to be distributed to some of our
clerks.

For each day, the clerk is to log tasks worked on for the day, (i.e worked
on the johnson account).
The clerk may work on a particular task for multiple days.

So far pretty easy, here's the kicker:

We want to create a query which reports the tasks worked on for that month.
So, if the clerk worked on the johnson account for 6 days, and the smith
account for 4 days
the query should only show on instance of johnson and one instance of smith.

Here's what I got so far:

3 Tables:
Daily Tasks
Weekdays
FedHolidays

Layout of Daily Tasks (Sorry if these columns don't line up evenly)

Task ID Date WeekdayID FedHolidayID Task1 Task2
Task3 Task4
1 1/1/2004 Thursday New Years
2 1/2/2004 Friday
Johnson Smith Filing Bush
3 1/3/2004 Saturday
Clinton
4 1/4/2004 Sunday
5 1/5/2004 Monday
Meeting Reports Smith Johnson
~
19 1/19/2004 Monday MLK Jr Day Bush


The WeekdayID and FedHolidayID are lookup values.

Question 1:
How can I create a query that will list the following tasks in one list, for
January:

Johnson
Smith
Filing
Bush
Clinton
Meeting
Reports

Question 2:
I want to count the number of weekends worked (i.e. Sat 1/2/04 & Mon
1/19/2004)

Question 3:
How would you handle sick days?

Question 4:
Because this project is still in the design phase, any design input would be
appreciated.

Don't really know how to use vba, unless someone can write the code for me,
I'd like to use the "design view" for this.

Thanx and regards,
"T"

 

[Tom Ellison]

Dear User:

The table design from which you started isn't adequate. If you want
to be able to query tables effectively and efficiently, they must
first be normalized. When you show columns like 'Task 1', 'Task 2',
etc. this is a pretty clear sign that it is not normalized.

I would suggest something like this:

ClerkTask
TaskID
Clerk
TaskDateTimeStart
TaskDateTimeEnd

Make a separate table of Holidays, just recording the date and holiday
name.

If you need to number the tasks in the day as Task 1, Task 2, etc.
then add a TaskNumber to the ClerkTask table above. However, you'll
always be able to assign a number, ranking these by TimeStart and get
such a number.

Your report will just need to filter the above for a period of time
and then you can report DISTINCT to omit duplicates. Also, you could
aggregate the hours over Task and Clerk and you'll get hours worked
without duplicating Task or Clerk. The query results you want are
VERY EASY if the tables are designed correctly first.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Test User]

Tom,

Thanx for you suggestions, I've dropped the task1, task2....columns and
replaced them with taskid

Thanx,
"T"