Time Sheet Setup

[Limbofrog]

This is more a question for a basic approach more than details.

I am trying to set up a time sheet for my office but I am having a mental block about how to go about it. My problem is there are too many variables that I could call a main variable. But maybe you guys/girls can help me with some good ideas...


Here is the deal:

-We have lots of jobs each having a unique 4-digit job number. Each job has a client number that hired us.

-Each employee has a unique 2-diget number.

-The sheets are currently done weekly in excel, each week shows a list of every job an employee worked on, the phase numbers and the times for monday, tues, wed, thur, fri, sat, sun. Since they are done each week and the days are also recorded, you can literally assign hours for each job to each day of the year if you wanted.

-Each timesheet job entry also has a Phase number and a Subphase number - not a problem.

Starting to sound like office space =)



So what we have now is this(if we use date as the main/first variable):


Date, Employee#, Job#, Client#, Phase#, MonHours, TueHours, WedHours, ThurHours, FriHours, SatHours, SunHours, Total Hours.

[I will want to be able to sort jobs by number, date, or employee or some combination - no prob, queries do that ok]


Now my problem is this. For the data entry, people will have a stack of excel paper timesheets for all employees. They will enter all the records for each employee on timesheet at a time.

I want the entries(or records) to show up on the entry form they are using so that they can look down and compare the form they are on to the paper timesheet and make sure they have entered all the records and only once.

Now we have 30 or so employees so I don't want to make a separate form for each of them but I want to keep it as simple as possible.

I thought of using a query to create a list of entries that only show the entries with that employee number and that weeks date. This would create a list that would match the printed timesheet but I don't know how to make the entry form(which can only be on one record at a time to show all the records in that query. I might be able to write a macro that would cycle through and search for them, but this seems like a bad direction and there might be a better way.


Got any ideas? Need more info?

thanks,

 

[Joe4]

This sounds very much like a project I worked and it looks like you have the same inefficiency as we did, i.e. employees enter time in an Excel sheet, print it out, then staff manually enter time into database. Not only is it inefficient, but it also opens the possibility of re-entry errors for the staff entering the data into the database.

What we did is we made our Excel sheet a standard template that everyone has a copy of on their hard drive. At the end of the week after they have entered in all their time, they click on a button which runs a macro that exports their entries to a text file on the network.

Then format of the export file is set up that it can easily be imported into our database table. Much faster, and virtually eliminates the possibility of re-entry errors.

Note, that in setting up your Excel macro to create the export file, it is best to work backwards. That is, determine how the data needs to look to import easily into Access, and build the export macro so that it puts the data in that format.

BTW, the project was a huge success, as it saves our staff hours a week!

 

[Limbofrog]

That is definitely a good idea. I like it. It does not solve my problem within Access, but it helps on the front end of it. If I can get my users have some level of common sence, I should be able to get that to work =)

Even clicking a button can get confusing... Do I click it once, or double click? That's what I will be hearing.

Thanks,

Greg

 

[Joe4]

The query works fine when you type in the search criteria.

You know the old saying: If you make something idiot-proof, someone will build a better idiot!

 

[Limbofrog]

They have already been built and they already work here with me =)

It's not all that bad, but sometimes I feel that way.

Greg