Company Timesheet Dilemma

[snackcakes101]

I've thrown this out there before but got WAY sidetracked with a million other projects. Now I'm ready to revisit it and I'm not that much smarter this go around!

Currently we use Excel spreadsheets - one file for each of the 10 employees. These spreadsheets have one line for each 30-minute increment - Sunday to Saturday. Each week a new spreadsheet is created via a Macro for the following week for each employee.

As a temporary solution, I have a "holding" database that imports these spreadsheets each week, cleans up blank records, validates job numbers and spits out all sorts of reports. Then all records get dumped into a "history" db and that's that.

What I need is this:

A system for employees to sign into every day and fill in their activities in 30 -minute increments. The records need to be there for them to fill out, THEY ARE NOT ALLOWED TO CREATE THEIR OWN. At the end of the week they will "submit" it; this will validate all job numbers and I will know they are ready for reports. I can do all the validating scripts, reports, etc.

What I can't figure out is what the db needs that will house all the live data. Do I need one record for every 30-minute time slot, every day of the year, for every employee? Then control what the employees see through portals and finds based on their initials?? I have an employee database already in place. Since they are not allowed to create their own records, all must be created and there for them to see based on employee and day of the week.

The portals are confusing me. We don't do "total hours worked on each job", we bill everything in 30-minute increments, so there is little flexibility in appearance.

[snackcakes101]

Well THAT failed... in the portal time entry database when I create a record for myself it shows every record with my initials attached to it in the related db, regardless of what week number I enter. I thought I could put "DAT" "Week 26" and it would only bring up the records for this week. My relationships are "Emp Initials" and "Week No."

Should I only have 1 week's worth of records in the related db for each employee? Can I force it to automatically create the 753 records with the following week's corresponding dates and week # each Monday morning??

I seem to grasp the most complicated scripts and calcs, yet I can't get over this hump. Once I put that in place I can do all my views, access levels, validations, reports, etc.

[QuinTech]

I would probably make a file each record of which represents one day for one employee (2 people working 3 days results in 6 records). In that file would be the fields EmployeeID, DayID (really just the date), TimeBlock, ClientID, and Detail. TimeBlock is a repeating field with up to 48 repetitions, one for each possible half-hour block in a day; let's say we enter values in here such as 0800, 0830, 0900, ... 2300, 2330. ClientID and Detail are also repeating fields with up to 48 reps, corresponding to the client services are being performed for during a particular half-hour and Detail describing the work done during that half-hour.

In the Employees file and the Days file, make a concatenated calc = EmployeeID & "|" & DayID. Make a relationship between the two and base your portal off this. Now when an employee is viewing her record, she only needs to modify the date to change the contents of the portal.

In this case, i'd set the relationship to allow creation of related records so that you don't have to create all 265 days in a year for each employee. However, if you need to you could disallow that and just make sure all the days pre-exist. You could periodically flush this file to a backup if you don't like its size.

Additionally, the Sum function and things like that will work on your repeating fields.

Does this help?

Jerry

[snackcakes101]

I never thanked you...I got swept away on another project and I'm finally recovering. Thanks for all your help! I was able to take many gems from your post and actually combine them with current solutions.

I appreciate it so much.