Calculations between records in a table

[awill110]

First of all, I am totally new to Access and have a decent amount of experience with Excel. I do not know VBA or SQL.

I have taken on what I thought was going to be an easy project. Our Accounting department currently does a lot of manual work and uses a lot of paper, so I thought an Access database would be perfect for their needs. They currently download a report from one payroll system, do a bunch of reformatting and calculations manually in Excel and then upload a CSV file into another payroll system. They also keep all of the reports that they download and upload printed in a binder for reference.

My goal is to create a database that will pull the data from the downloaded file, perform all of the calculations and formatting, then create a CSV ready to be uploaded. The database would also have a reporting feature so they could go back and pull up the data for any pay period if they needed it. I am probably going about it the long way, but I think I have most of the work done to get the results that I need. My main hang up right now is a calculation from the downloaded report.

I get all hours for the employees as separate records in the table. I need to take the vacation hours, sick hours, and so on for each salary employee and subtract them from the 40 hours per week that they get paid for. Here is an example of the data that I get:

Company Code	Department	Last Name	First Name	Employee	Earnings Code	Worked Department	Pay Rate	Hours	Dollars	Description
DAK	101-TECH	Johnson	Stephen	DAK100999	REGSAL	101-TECH	0	40	0	Salary Regular Hours
DAK	101-TECH	Johnson	Stephen	DAK100999	VACATION	101-TECH	0	12	0	Vacation
DAK	101-TECH	Johnson	Stephen	DAK100999	SICK	101-TECH	0	8	0	Sick

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

In this example, I would need to subtract the record for sick time and the record for vacation time from the first record. I am totally stuck and don't know the best way to proceed with this. Any and all help is greatly appreciated!

 

[Micron]

My goal is to create a database that will pull the data from the downloaded file, perform all of the calculations and formatting

Sounds to me like you might have the same problem that many other Excel afficianados do - a spreadsheet mindset about databases. However, I might be making incorrect assumptions on what you mean by calculations and formatting. All calculations on data are supposed to be done at the form or report level - storage of calculated data is NOT recommended. But perhaps to further try to answer your question, the data you show should be parsed into tables according to good database design practices (normalized) and worked with from there. However, I suspect that would not serve your final purpose since you need to pass the data on to some other software. In all, I'm not so sure that Excel is not the best tool for your needs. It can be quite robust by augmenting its formatting/calculation power with vb, which you will no doubt need if you stick with an Access solution. The only thing is, they are not exactly the same in terms of their object models.