Excel and Access - tracking payroll

[Sarah]

I am looking for a way to take multiple timesheets and track employees total
hours worked, vacation/sick time taken etc both cumulative and by week.

Please advise.

 

[George Hepworth]

Very general questions usually elicit very general responses....

You mention both Excel and Access. Is it the case that your timesheeets are
in Excel? Or are they in Access?

 

[Larry Daugherty]

These microsoft.public.access(.*) newsgroups exist to help people
resolve their specific technical hurdles. You aren't there yet.
Implicitly you've asked for the design of an application that meets
your incompletely specified needs.

If you are seeking professional help in obtaining an application that
meets your needs then I suggest that you advertise in your local
market or on the web. Don't post job requirements in the technical
newsgroups.

If you want to learn to develop applications using Microsoft Access
then Welcome to a world of fun. I recommend that you begin to lurk:

microsoft.public.access.gettingstarted
microsoft.public.access.tablesdesign

Also, visit www.mvps.org/access and poke around picking up bits of
wisdom.

You should also visit your local bookstore and get a gook at the
beginning level of Access. Be forewarned that Access has a long,
steep learning curve. It is also worth it if you stick with it long
enough to be productive.

Post into the specific newsgroup(s) as you have specific questions.

HTH

 

[bsmith59]

I am looking for a way to take multiple timesheets and track employees total
hours worked, vacation/sick time taken etc both cumulative and by week.

Please advise.

Hi Sarah,

From your subject line, it sounds like you are planning to do payroll

using Access or Excel? If you are just planning to do tracking, and
already have an off-the-shelf solution (or ADP), that seems like a
good use for Access or Excel. But if you're planning to actually do
payroll with any of these tools, I'd advise against it. Not only
since there are many existing products (Quickbooks, ADP, etc), but
from a liability and reliability perspective. Calculating payroll,
including taxes, can be pretty ugly.

Brandon
http://accesspro.blogspot.com/ (access tips for non-programmers)

 

[Sarah]

I do appologize for the general question, it was friday afternoon and I was
just looking to get out of work..you know how it is!

What I am looking to do is take our timesheets, which are done in excel and
dump the information into other spreadseheet (or databases) so I can easily
track within the year how many sick/vacation etc. hours have been used.

I think I have found a way now, but I feel as though I have too many steps
and know there has got to be an easier way.

Any ideas?

 

[bsmith59]

I do appologize for the general question, it was friday afternoon and I was
just looking to get out of work..you know how it is!

What I am looking to do is take our timesheets, which are done in excel and
dump the information into other spreadseheet (or databases) so I can easily
track within the year how many sick/vacation etc. hours have been used.

I think I have found a way now, but I feel as though I have too many steps
and know there has got to be an easier way.

Any ideas?

Hi Sarah,

Sounds like you want a way to automate the process of pulling the data
into a single place from your last sentence. Several routes (probably
more, of course) you could take:
1) experiment with using Macros in Excel to pull data from one open
spreadsheet into your "master tracking" spreadsheet. You'll have to
make some modifications to the macro so it references the other open
worksheet generically (as I'm assuming your timesheets will have
different names).
2) Create a VBA routine in Excel or Access (I prefer Access for this
type of activity) that recurses (i.e., "loops through") the list of
spreadsheets in a staging directory and imports the data from each
spreadsheet into your master tracking table.

Once you have the data loaded, you can create reports and run queries
against it. In Excel, that can be relatively straightforward, but
somewhat limited in how you can look at the data. In Access, you can
do more with the queries, but it can take a bit more to learn.

Here's what I would suggest for next steps:
1) Figure out what your level of commitment is to creating this, and
your current level of expertise with Excel, Access, macros, and VBA.
And what your interest in learning more is. That way you can gauge
which path you want to take (essentially the time you'll spend
learning to automate vs. time you save on a partially automated
solution).
2) Jot down the anticipated operating environment. In other words,
what will the inputs look like before your solution starts processing
the timesheets. for example, your design (and advice you'll receive)
will depend on whether you have a single timesheet to import each
week, or a single Excel file for every employee each week.
3) Jot down your requirements. For example:
a) The solution needs to import data from timesheets into a
central location.
b) The solution needs to be able to calculate # of days of
sick and vacation taken per individual, per month or quarter.
c) The solution needs to be able to calculate the # of Mondays
or Fridays of sick time taken per individual.
d) Etc.

Then feel free to share and I'm happy (I'm sure others are too) to
help you through this. It's fun when you have an end product that
makes life easier! That's for sure.

Cheers,
Brandon
http://accesspro.blogspot.com (tips for non-programmers)

 

[Sarah]

Hi Sarah,

Sounds like you want a way to automate the process of pulling the data
into a single place from your last sentence. Several routes (probably
more, of course) you could take:
1) experiment with using Macros in Excel to pull data from one open
spreadsheet into your "master tracking" spreadsheet. You'll have to
make some modifications to the macro so it references the other open
worksheet generically (as I'm assuming your timesheets will have
different names).
2) Create a VBA routine in Excel or Access (I prefer Access for this
type of activity) that recurses (i.e., "loops through") the list of
spreadsheets in a staging directory and imports the data from each
spreadsheet into your master tracking table.

Once you have the data loaded, you can create reports and run queries
against it. In Excel, that can be relatively straightforward, but
somewhat limited in how you can look at the data. In Access, you can
do more with the queries, but it can take a bit more to learn.

Here's what I would suggest for next steps:
1) Figure out what your level of commitment is to creating this, and
your current level of expertise with Excel, Access, macros, and VBA.
And what your interest in learning more is. That way you can gauge
which path you want to take (essentially the time you'll spend
learning to automate vs. time you save on a partially automated
solution).
2) Jot down the anticipated operating environment. In other words,
what will the inputs look like before your solution starts processing
the timesheets. for example, your design (and advice you'll receive)
will depend on whether you have a single timesheet to import each
week, or a single Excel file for every employee each week.
3) Jot down your requirements. For example:
a) The solution needs to import data from timesheets into a
central location.
b) The solution needs to be able to calculate # of days of
sick and vacation taken per individual, per month or quarter.
c) The solution needs to be able to calculate the # of Mondays
or Fridays of sick time taken per individual.
d) Etc.

Then feel free to share and I'm happy (I'm sure others are too) to
help you through this. It's fun when you have an end product that
makes life easier! That's for sure.

Cheers,
Brandon
http://accesspro.blogspot.com (tips for non-programmers)

 

[Maelinar]

On account of you've asked me what I'd do:

I would not bother with an access database to solve this problem. Excel can
track the information you desire sufficiently from within the application.

My approach would be to consider using subtotals of the information you are
wishing to compile a result for, and to link to that subtotal in a 'master'
spreadsheet. (hit the equals button and click you way to the field(s) you
want)

If you are uncertain about where the information is that you are looking
for, or it is contained in fields that vary from week to week, I'd use a
vlookup or hlookup with a range.

For further help on excel code though, I'd post on the Excel discussion group.