Time Sheet database - automatic fill from previous week


S

Sierras

Hi,

I'm trying to do a timesheet database. It will show the hours worked
from week to week. The database is working fine as the user just pick
the employee, the date, and the hours worked. But this involves a lot
of data entry since it has to be done for each employee. Also, the
employee days of the week and times are almost always the same from
week to week. In Excell, the user would just make a copy of the
previous week spreadsheet and modify the few records that need
mending. But I can't find a way to do this in Access.

Is there a way to basically click a command button that will duplicate
all the hours from the previous week for all the employees and append
them into the hours worked table for the current week (or selected
week) and then present them in a form for editing?
 
Ad

Advertisements

S

Sierras

Well I found one way of doing this. I basically write a query for all the
employees for the last week. Then export the data to an Excell
spreadsheet. Then modify the dateworked field (ie. =A1+7). Then after
amending the few employee hours that need changing, I just copy and
paste-append the cells into the hours worked table. This works, but it
isn't pretty and I certainly can't ask the user to do all these steps. I
guess I'm looking for a macro or some code that will automate this or find
some other way to do this.

Anyone want to give this a try?

Thanks..
 
Ad

Advertisements

S

Sierras

Ever get the feeling no one is interested in what you're doing?
Anyway, after a lot of reading, I found a better way to do this within
Access. But I'm still hoping that someone out there has a more clever way
of doing this.

I have an hours worked table: tblhoursworked with 3 fields (there's
actually more, but this should be enough for my example)
employeeid
hoursworked
dateworked

I created a temporary table that is a duplicate of the tblhoursworked
called tbltemphoursworked
employeeid
hoursworked
dateworked

So now I run a delete query to delete all the records in the temp table.
Then I run an append query from the hoursworked table to the
tmphoursworked table for just the dates that the user wants to work with.
(Usually the week prior)
Then run an update query on the tmphoursworked table to add 7 days to the
field dateworked.
Then present this table in a form to allow the user to modify any records
in the tmphoursworked table.
After the user is satisfied with the info in the temp table, he then
presses an append query button which takes all the data in the
tmphoursworked table and appends them to the tblhoursworked table.

WOW - this seems to work. But like I said, I'm still hoping that someone
has a more clever way of doing this. I'm a little afraid that the user
accidentally messes up the live hoursworked table with all this automation
and too many button clicks.

Thanks...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top