Cathy,
I have a solution which assumes:
A table with monthly salary per employee:
[tblPayroll]
Employee
Salary
A table with the available denominations:
[tblDenominations]
Denom
(records: 50000,20000, 10000, 5000, 1000; can change as required, see note
after next table).
A temporary table (amnually created at first) to store the denomination
breakdown:
[tblDenomination_Breakdown]
Employee
Salary
50k
20k
10k
5k
1k
Here I have assumed denominations as above, but you can change; the
important thing is to have as many fields (in descending order) as the
denominations declared in tblDenominations.
The following sub in VBA clears the existing records in tblDenominations,
creates new records for employees / salary reading them from tblPayroll (so
it will capture staff/salary changes on every run) and finally calculates
and populates the denominations per employee:
Sub Calculate_Payroll_Breakdown()
Dim db As DAO.Database
Dim rDnm As DAO.Recordset
Dim rBkd As DAO.Recordset
Set db = CurrentDb
strSQL = "DELETE * FROM tblPayroll_Breakdown"
db.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO tblPayroll_Breakdown ( Employee, Salary )"
strSQL = strSQL & " SELECT tblPayroll.Employee, tblPayroll.Salary"
strSQL = strSQL & " FROM tblPayroll"
db.Execute strSQL, dbFailOnError
strDenom = "SELECT Denom FROM tblDenominations ORDER BY Denom DESC"
Set rBkd = db.OpenRecordset("tblPayroll_Breakdown")
rBkd.MoveFirst
Set rDnm = db.OpenRecordset(strDenom)
Do Until rBkd.EOF
rBkd.Edit
rDnm.MoveFirst
remd = rBkd.Fields(1)
For i = 0 To rDnm.RecordCount - 1
rBkd.Fields(i + 2) = remd \ rDnm.Fields(0)
remd = remd - rBkd.Fields(i + 2) * rDnm.Fields(0)
rDnm.MoveNext
Next
rBkd.Update
rBkd.MoveNext
Loop
rDnm.Close
rBkd.Close
Set rDnm = Nothing
Set rBkd = Nothing
Set db = Nothing
End Sub
Paste the code in a (new or existing) general module. It requires an
appropriate DAO reference to run (while in VBA editor, go Tools > References
and see if there is a DAO 3.X Object Library among the ones checked at the
top. If not, scroll down to find it and check it; "appropriate" is 3.51 for
A97, 3.6 for A2K or later). Run the code from within the VBA editor to test.
If satisfied, you can call it from a command button on a form.
There are many changes that can be made, especially as regards the design of
the main table with the payroll information. For instance, if you add
records to it month on month (so the re is a month field in there), the code
can be modified to select a particular month for the breakdown table etc.
HTH,
Nikos
