TimeSheet in MS Access

[mgorelkin@gmail.com]

I'm new to MS Access, and was able to familiarize myself with the
basics. I am, however, struggling with the following problem. I am
trying to create a timesheet type form where the sheet contains 7
columns (Sunday, Saturday, Monday, ... Friday), and the rows are 30
minute time fields (7:00am, 7:30am, ... 6:00pm). Each cell should be a
combo selection from an activities table. This sheet is for a
particular week, so hitting next on the form's control should go to
the next week. Can anyone help me design this in terms of
tables/queries/forms?

Much appreciated,
Maxim

[Alan Webb]

Re: TimeSheet in MS Access

Maxim,
I've got one I did for myself. If you *really* want a two dimensional
matrix of 336 cells containing combo boxes of activities then a table with
the days of the week and 48 rows for each half hour would work. Each field
would have to be set up as a lookup field that would look at your activities
table for its values.
I have a couple concerns, though. First, 336 cells is a lot for a user to
look at and a big enough number that the odds on data entry errors begin to
be significant. Second, as presented you couldn't use the matrix as a
source of payroll data because it doesn't capture the calendar date worked.
Third, people come and go and you have to remember who was doing what when
for each employee, both current and former employees. And (assuming this is
a professional services organization), clients are going to expect you can
document the activities an employee worked on--both historical and current.
With only a single matrix of seven columns and 48 rows you have not
accounted for multiple days, multiple employees and multiple projects.
Fourth, change happens, and changes get made to data to reflect this. A
client may choose to rename an activity in the middle of a contract, a
dispute over billing may result in a change in the contractual relationship
that affects how much an activity is worth in the middle of a pay period . .
.. Any number of things may change in the lifetime of a project that would
affect salary. It is critical to know for each significant point in time
what the state of the data was at that point in time. One form with a
week's worth of activities won't be enough.
I made a table of seven columns and an unlimited number of rows. My table
has Project, Employee, Date Worked, Time In, Lunch Out, Lunch In, Time Out.
This is enough for my to track my own salary fairly well.
--
Alan Webb
knoNOgeek@SPAMh otmail.com
"It's not IT, it's IS"

<mgorelkin@gmai l.com> wrote in message
news:1114970133 .015600.321010@ g14g2000cwa.goo glegroups.com.. .[color=blue]
> I'm new to MS Access, and was able to familiarize myself with the
> basics. I am, however, struggling with the following problem. I am
> trying to create a timesheet type form where the sheet contains 7
> columns (Sunday, Saturday, Monday, ... Friday), and the rows are 30
> minute time fields (7:00am, 7:30am, ... 6:00pm). Each cell should be a
> combo selection from an activities table. This sheet is for a
> particular week, so hitting next on the form's control should go to
> the next week. Can anyone help me design this in terms of
> tables/queries/forms?
>
> Much appreciated,
> Maxim
>[/color]

[(Pete Cresswell)]

Re: TimeSheet in MS Access

Per mgorelkin@gmail .com:[color=blue]
>I'm new to MS Access, and was able to familiarize myself with the
>basics. I am, however, struggling with the following problem. I am
>trying to create a timesheet type form where the sheet contains 7
>columns (Sunday, Saturday, Monday, ... Friday), and the rows are 30
>minute time fields (7:00am, 7:30am, ... 6:00pm). Each cell should be a
>combo selection from an activities table. This sheet is for a
>particular week, so hitting next on the form's control should go to
>the next week. Can anyone help me design this in terms of
>tables/queries/forms?[/color]

Been there.

It's not trivial.

In my experience, you're talking about a pivot table presentation of something
like:

tblTimesheet
tblHours

I did mine against SQL Server, so I used a stored procedure to flip the data.
Same thing can be done locally by setting up a few work tables in a C:\Temp work
DB and working on them with VBA code.
--
PeteCresswell