Timesheet

[biggcc]

I've searched through this forum and found one thread that I thought would help me but have not heard back from the post I left requesting help so I thought I would try my luck here.

What I am trying to do is create a very simple timesheet for our employees to use. Basically we turn in timesheets twice a month - on the 15th and the last day of the month. Currently what we have is an Excel file for each user that has 12 sheets - one for each month with macros that print sheets for each half of the month.

What I've been assigned to do is shift our timesheets over to an Access database so that we don't have to recreate a new timesheet every year for each employee.

I have tables created for the users and the projects but can't really seem to figure out how I should create a table to hold the hours work for each day. In other words our timesheets basically look like this:

User: Mr. Smith
Period Ending: 1/15/2006

Projects 1/1 1/2 1/3 etc. Total
XYZ 5 4 5 14
ABC 2 4 1 7
123 1 2 3
_________________________________
Totals 8 8 8 24

Don't know if the above will look ok so I've attached a screen print of part of our current timesheet.

Any ideas would be greatly appreciated as I'm not the sharpest Access guy around.

 

[Sammy B]

I made up a dummy db showing how I would handle the tables.
Let me know if this works for you.

Sam

 

[biggcc]

Emp Timesheet

Thanks Sammy B but I don't know if that's going to work for me.

I've created a form screen of what I think it should look like and attached it here. I don't know if its possible to do - and I'm probably lacking in the needed skills to do it - but I need to create an interactive type of timesheet.

Here's how everything works here in a nutshell. We collect timesheets bi-monthly - on the 15th and last day of the month which coinsides with our pay periods. The first timesheet is for the first 15 days of the month and the second timesheet is for the remainder of the month.

What I would like to create is an database where a user will open a blank form then from dropdowns they will select their name and the period ending for this timesheet. Once the period ending is selected I would like it to fill in the actually dates they are entering time for. Then they would select the projects they have worked on and enter the appropriate hours for each day.

Also now that I think about it they would probably need the ability to save this timesheet until they are ready to print it.

Does this sound crazy or does it sound like something that can be done with alot of work?

 

[LLB]

I made up a dummy db showing how I would handle the tables.
Let me know if this works for you.

Sam

Can your sample be posted in A97 as well. Would greatly appreciate it. Thanks.

 

[The_Doc_Man]

The problem with this problem is that it is taking an Excel flat-file layout into an Access relational file. When you do that, you find that the Excel file layout leads you to something that will very quickly violate normalization if you try to make it look TOO much like Excel. (This is a very common pitfall for any Excel-to-Access conversion.)

Basically, the "correct" way to store someone's timesheet data involves entries such as...

TSE:
PersID: (person's ID in system, probably a foreign key to a Personnel table)
Project: (project's ID in system, probably a foreign key to a Projects table)
WorkDate: Date on which work was done
WorkHours: Number of hours worked (probably SINGLE but could be DOUBLE).

Now, the trick is to populate your timesheet. This could get EXTREMELY tedious. You would probably want a master form containing today's date and a list of time entries as a continuous sub-form. The sub-form can be designed to use a drop-down (as a list box, e.g.).

To print the timesheet, what you want is a query that selects only those entries between two dates (start and end of pay period), then do a cross-tab query on that query (for current period) or directly on the underlying table (for Year-To-Date or Year-To-First-Day-On-the-Job).

This problem is extremely awkward because you are trying to build a DB based on a sparse calendar. That is, not every day will have a record for every project and every employee allowed to work on that project. It is that sparsity that is going to eat your socks and make a lot of work for you.

Frequently, this sort of thing is done with a third-party package designed to look like a spreadsheet. Also, you don't list your country of origin, but in the USA, if you are dealing with personnel pay, your system must be audited and protected with all sorts of tight security controls. One or more of our federal labor laws, I believe.

Now, there is still the chance that you could let a person use a timesheet, after which you could write some VBA code to import the timesheet, pull out the cell contents, and synthesize the entries I described above. That is a rather ambitious project for a person unfamiliar with Access. Not impossible, but definitely ambitious.

 

[Dreamweaver]

I think I have something somewhere that allows for all that I'll take a look Think It's part of an old program For DJ's

Back Later

Mick

 

[Dreamweaver]

I have found it In my old project system which now I remember I will be adding if to my new project System So When I get there I'll see bout puting it all into an example.

This is a view of the viewer set to show the current week the times are add by a form like the Progject one.

Soon As I get It Sorted I'll Give Ya A Shout it you havent got it sorted by then.

Mick