Access capabilities

[viceb]

I've used Excell extensivly but I'm stuck on a project and I would just like to know if Access is what I need.
I have a Time-card spreadsheet that each employee uses for their weekly payroll. The timecard allows employees to breakout their time by job. I want to create a database which will pull data from the weekly timesheets (18 total) by job and maintain that data..then allow me to query a given job so that it has pulls from the stored data for lets say a years worth of timecards.
I tried dumping the timecards in one directory and create a new sheet inwhich I put in the job number and it opens all of the timesheets and pulls time from each employee from each week. I am failing trying to figure out how to do it with for next loops that opens the sheets by employee and looks for each job..times 18 employees time 52 weeks.

Would Access allow me to do this? If so, I'll get Access and get a crash course.

Thanks

 

[pocquet]

Hi,

Short answer, Yes.

Long answer you can use a single access database to link to spreadsheets as data sources. Then create append query's for each sheet to take the data from the worksheet through to a history table. Then you can build your querys off the history table.
You will need to use the same sheets over and over (or same named sheets) else the link will be broken.
I had to operate something similar that had 50 sheets linked. I used a macro and task scheduler to run over night and import the data. I also had a macro in each sheet that would clear the old data to allow for new data to be entered.

 

[Joe4]

In Access, all the data would be contained in just a few tables. In your simple example, you would have the following tables:

Employee Table
- Employee ID
- Employee First Name
- Employee Last Name
- Any other employee level data you may want (address, date of birth, department, etc)

Hours Worked Table
- Employee ID
- Week Number
- Date Worked
- Job Number
- Hours Worked

So, the two tables would be joined on the Employee ID field. You may also want a unique ID field in your Hours Worked Table (AutoNumber would suffice).

Then no For/Next loops are necessary. You can do queries on your Hours Worked Table to get the information that you need. Some of those may be Totals queries, where you group on certain fields, and get the Totals of other fields (for example, if you grouped on Employee ID and Job Number, and then totalled the Hours Worked field, it would list the total number of hours that each employee worked on each job.

Note, if you have extra information on each Job Number (maybe like descriptions or other information), then you will probably want a Job Number table too.

 

[viceb]

Thanks to both of you for the quick reply.

Just so I can wrap my head around it...the database that I create...lets say at the end of the pay period, I dump a copy of each employees spreadsheet (which is unique with that weeks date) into a directory.
Can I assume:
Access can query each sheet and pull the data by job (that emp worked that week on that job for that many hours).
Then I have a query sheet to punch in a job number and spit out the data of who worked that job, when and how many hours.

So the question is: Do I then need to keep the weekly spreadsheets or once the data is sucked in, its now in access...and I clear out the sheets and do the procedure again next week. So the database grows at it sucks in the data...but I don't need to keep all the sheets in an active directory to pull from?

In Access, all the data would be contained in just a few tables. In your simple example, you would have the following tables:

Employee Table
- Employee ID
- Employee First Name
- Employee Last Name
- Any other employee level data you may want (address, date of birth, department, etc)

Hours Worked Table
- Employee ID
- Week Number
- Date Worked
- Job Number
- Hours Worked

So, the two tables would be joined on the Employee ID field. You may also want a unique ID field in your Hours Worked Table (AutoNumber would suffice).

Then no For/Next loops are necessary. You can do queries on your Hours Worked Table to get the information that you need. Some of those may be Totals queries, where you group on certain fields, and get the Totals of other fields (for example, if you grouped on Employee ID and Job Number, and then totalled the Hours Worked field, it would list the total number of hours that each employee worked on each job.

Note, if you have extra information on each Job Number (maybe like descriptions or other information), then you will probably want a Job Number table too.

 

[Joe4]

You don't have "sheets" in Access. You will be importing all the data into one table (your Hours Worked Table). If you continue to have them enter data into an Excel sheet, you may need to create a little macro to export the data in the right format, as the data structure will most likely be different. In Access, you will have a separate record (row) for each Employee, Job Number, and Date Worked. So you only have one hourly amount per record (row).

If your staff can have access to Access, I would recommend creating their timesheet as an Access form. Then they can enter it directly into Access, and you won't have to do any importing.

There are various Access templates/sample database our there on the internet. If you search, I bet that you can find some TimeSheet examples that you can play around with.

 

[viceb]

Hopefully, last question. Since I'm not fully versed in Access...but the idea of moving the timecard to an Access input page: Via visual basic calls, marcos, whatever...can the access input page still give the same kind of functionality as an Excel sheet can? For example, calculating time used based on time inputs (9:00~11:00, 11:30~4:30,etc)...and calculating overtime past 40..etc

You don't have "sheets" in Access. You will be importing all the data into one table (your Hours Worked Table). If you continue to have them enter data into an Excel sheet, you may need to create a little macro to export the data in the right format, as the data structure will most likely be different. In Access, you will have a separate record (row) for each Employee, Job Number, and Date Worked. So you only have one hourly amount per record (row).

If your staff can have access to Access, I would recommend creating their timesheet as an Access form. Then they can enter it directly into Access, and you won't have to do any importing.

There are various Access templates/sample database our there on the internet. If you search, I bet that you can find some TimeSheet examples that you can play around with.

 

[Joe4]

Hopefully, last question. Since I'm not fully versed in Access...but the idea of moving the timecard to an Access input page: Via visual basic calls, marcos, whatever...can the access input page still give the same kind of functionality as an Excel sheet can? For example, calculating time used based on time inputs (9:00~11:00, 11:30~4:30,etc)...and calculating overtime past 40..etc

Yes, and the beautiful thing about Access Forms is that they are much easier to work with than Excel Forms. With Excel Forms, you need to use VBA to explicitly map all entered items to your spreadsheet. In Access, you can make the fields on the Form "bound" to your Table fields, so you do not need to use any VBA to explicitly map them. Once I learned Access, I completely stopped using Excel Forms and used Access instead.
<body id="************" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">Hopefully, last question. Since I'm not fully versed in Access...but the idea of moving the timecard to an Access input page: Via visual basic calls, marcos, whatever...can the access input page still give the same kind of functionality as an Excel sheet can? For example, calculating time used based on time inputs (9:00~11:00, 11:30~4:30,etc)...and calculating overtime past 40..etc </body>