How to compare hours, per date, amongst 3 sources

[Marq]

Using Access 2010

I am currently using Excel 2010 to do this, and want to incorporate it into my access database and get excel out the picture all together:

One aspect of my job is to make sure the hours worked per day all match between 3 sources: Timesheets; Progress Sheets; Internal program our comonay uses.

How this all plays out is like this:

I give excel progress sheets to our guys in the field and each project they work on has its own progress sheets....and on these individual progress sheets, the activites for that particular project are listed with estimated man hours......next to each activity, the foreman writes down the hours worked that day for that activity.

Then, the foreman fills out the timesheets which have this same info but its a formal timesheet that gets hand in to our time keeper.

Once the timekeeper gets the timesheets she then plugs the hours into our "in house" pc program.

One thing I do is, daily, compare all 3 sources to make sure the hours are matching in all 3 places and charged to the correct job. I created an excel work book that links to the progress sheets which the top column in each progress sheets has the date starting in cell Q1 and going all the way to IV1.....and as the foreman turns progress into me daily i plug the hours they worked that day next to the activity.

This main excel workbook i uses to make sure all the hours are matching is linked to each individual progress sheets summed up hours per day...its on a tab called "PROGRESS"...thats how the PROGRESS hours are fed into the main work book.

I then get a copy of the timesheets for that day..and on the "TIMESHEET" tab i manully plug in the hours worked that day per job.

and then finally i run a report from our inhouse system and manulaly plug into the main work book "SYSTEM" tab.

on the 4th tab is "COMPARISON"....and this is where all the matching takes place. One job will look something like this (hope it comes out correct..im going to type how it supposed to look):


8-1-11 8-2-11 8-3-11 8-4-11


E-7886 Timesheet 40.5 73 15 36
Progress 40 73 15 38
System 40.5 73 15 38



As you can see (hopefully) this lets me see all 3 sources where the hours are plugged and lets me see if there are any discrepencies. you can see on 8-1-11 my progress is off .5 hrs......and on 8-4-11 my timesheet is off by 2 hours......i would then investigate all 3 sources to see which is correct and make changes as required. I've been doing it this way for years in excel but want to do this in access......which would me making my progress sheets in access too but that will come lateer on....right now i just want to see if the aove can be done exactly how i have i showing up in excel (i.e the format above).

I know it may seem redundant but there are 3 sources where the hours must go and its manually captured by 3 different people and here are ALWAYS going to be screw ups which makes it frustrating for everyone....my system finds these mistakes quickly and is a useful tool.

so if anyone has something similar or can coach me briefly on how to make this i would greatly appreciate it.

Thanks...and i hope im clear in my descriptions.

 

[revans]

Just some thoughts. I would keep your 'actual' timesheets in excel. This lets your managers keep on with what they know and you won't have to install access on all their machines.

You can then import each of their excel sheets into access to do what you want with them. You can import each of the three into a temp table for that type of timesheet.

You would then have a form that compares the three, potentially with dropdowns to select the individual records or something. I see three subforms listing each set of data like you have it listed in your post. You'll need a good id system in place, either by employee, job, both or whatever so you can be sure you're looking at the right stuff. On this form you'd do whatever changes need doing. Then you would 'promote' the 'good data' to a permanent table/set of tables in Access for storage.

Each of these steps has its issues. A simple search on this site on "import excel to access" should net lots of info. I'd start with the table structure and playing with a form to see if you can get something workable then smooth things out from there. The promotion part would just be an update/append query based on the selections on the edit form.

hth,

Rich