Manipulate data

[Marcie]

I have a db that's primary goal in life is to calculate payroll salary's,
email info to staff and create budget assumptions. Our payroll system has no
way of storing all of the rules we have so I have created the db to fill the
void. At this time I pull all the information into access and then run
through about 13 queries to calculate all of the different rules. I am just
wondering if there is a better way to do this. Maybe passing all of the
information into module doing all the calculations and returning each persons
values. If that is the best way how would I pass all of the values into
module?
Thanks

 

[Daryl S]

Marcie -

The best way depends on the structure of your data and the rules you want to
apply. Queries that apply rules to the entire data set are usually faster
than running each record through code, but you are running 13 queries, so
maybe one pass through the data would be faster... Sometimes a combination
of the queries and code work best. If all of your data is in one table, then
it might be easy to do the calculations using code.

I assume you will make a backup of your database before you make any changes.

If you want to try doing it through code, you will need to do your
processing through a recordset. Look up help in Access on this - you can
open a table or query as a recordset. If all the data you need is in one
table, and the 'resuts' are placed back in that table, then you only need to
open the one table as a recordset. It can get more complicated if you need
multiple tables or queries open at a time, as the recordset may not be
updateable. In that case, you may need to open another recordset for adding
new records (i.e. the results) to as you loop through the source recordset.


Once you have a recordset open, you can loop through all the records in the
recordset (that is until the .EOF property [end of file] is true) and update
the fields in the recordset with the calculated values (or append the new
calculated data with the key values to the results table). Here is a very
basic structure of how the code will look...

Public Subroutine CalcSalary()

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tablename", dbOpenTable)

Do While Not rs.EOF
IF rs!HoursWorked > 40 Then
rs!RegHours = 40
rs!OTHours = rs!HoursWorked-40
Else
rs!RegHours = rs!HoursWorked
rs!OTHours = 0
End If
rs!RegPay = rs!RegHours * rs!HourlyRate
rs!OTPay = rs!OTHours * rs!HourslyRate * 1.5
... other rules
rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub