Ernst, hi!
Hmm, problem.
Possible solutions:
1 - to allow a single record for each employee within the relevant period,
you'd need to have a field for each type of time spent (eg 'normal',
'overtime' etc). I totally agree - not the best way forward, and it means you
(or, worse, another developer!) need to be called in every time the client
needs a different type of time coded in.
2 - set up multiple records for each employee, with one record for each type
of time recorded in the period. Yes, we're talking 'continuous forms' here.
And, unfortunately, you can't have a continuous subform within a continuous
form - therefore at any one time you'd only see one person's data.
As I see it, only option 2 is acceptable, even though it too has
disadvantages.
So each record would only contain one 'time type'. Then as I see it the
source query for the form would include the 'person table' (linked to the
tblPayroll via EmpID, I assume). The form header section could then show the
employee's name etc, with the detail section allowing date, hours, pay rate &
time type. You could set up some simple coding to copy any common data (date
and - if it's the same for all pay types - pay rate) each time you set up a
new record. You'd also have, I suggest, a separate table listing all the
valid 'time types' (normal, overtime, sick, ...) and use this to populate a
combobox for the user to select. (Make it LIMIT TO LIST = TRUE,
so that subsequent processing isn't scewed up by typos!!) You could then,
maybe, allow the user to add new time types to the lookup table, depending on
how you then derive pay from the recorded hours.
To move to other records for other employees, you could either:
- rely on tabbing through the records (by having the navigation buttons
visible);
- by having a lookup list
- by having an unbound combo with all the employee names and use its value
as the criterion for the query behind the form (plus a 'requery' button or an
'OnChange' event to force refreshing of the form data).
Unless you were using the form in 'Add Nrew Records Only' mode you'd also
need to think about filtering on date.
Is this helpful? - please let me know!
All the best
--
Laury Burr a.k.a. Doogle
Ernst Guckel said:
I'm assuming it's OK for the form to be read-only?
No actually I want to use it as a data entry form... Best way I can
describe it is spreadsheet like... Names down the side and hour types accross
the top... I can do this quite fine if I restructure the payroll table like
so:
tblPayroll
PayrollID(PK)
EmpID(FK)
PayDate
RegHours
OTHours
VacHours
SickHours
HolHours
PerHours
PayRate
but this will not allow any future pay types... Everything I am told says
DO NOT build it this way...
Ernst.
SELECT tblEmployees.EmpStatusID, tblEmployees.EmpFirstName,
tblEmployees.EmpLastName, tblPayroll.PayRate,
Sum(RegHours([Hours],[PayTypeID])) AS Regular,
Sum(OTHours([Hours],[PayTypeID])) AS OT
FROM tblEmployees INNER JOIN tblPayroll ON tblEmployees.EmpStatusID =
tblPayroll.EmpID
GROUP BY tblEmployees.EmpStatusID, tblEmployees.EmpFirstName,
tblEmployees.EmpLastName, tblPayroll.PayRate;
What you then need to do is to create the functions
RegHours(Hours,PayTypeID) and OTHours(Hours,PayTypeID)For example:
Public Function RegHours(Hours, PayTypeID) as double
If PayTypeID = 1 then
RegHours = Hours
Else
RegHours = 0
EndIf
End Function
(Assuming the 'PayTypeID' for regular hours is 1)
Good Luck - let me know if this answers your problem, and if you need any
further help.
--
Laury Burr a.k.a. Doogle
data dot dynamics at virgin dot net
Ernst Guckel said:
Hello,
I have a payroll database that requires a batch payroll form. I am having
a bit of trouble getting the functionality of it to work right... The source
of the form is:
SELECT tblEmployees.EmpStatusID, tblEmployees.EmployeeNumber,
tblEmployees.EmpFirstName, tblEmployees.EmpMiddle, tblEmployees.EmpLastName,
tblPayroll.PayrollID, tblPayroll.EmpID, tblPayroll.PayDate,
tblPayroll.PayTypeID, tblPayroll.Hours, tblPayroll.PayRate FROM tblEmployees
INNER JOIN tblPayroll ON tblEmployees.EmpID=tblPayroll.EmpID ORDER BY
tblEmployees.EmpStatusID, tblEmployees.EmployeeNumber;
The problem being is that I will get a seperate record for each PAYTYPEID
related to each employee... This is not what I want. I want to have a
continous form that lists the employee name, payrate, ect. as well as each
paytype on that same line... The way it is now I get a seperate line with
the employee name, payrate, ect each time...
If an employee has 40 reg hours and 10 OT hours I want:
EmpFirst EmpLast $10.00 'Regular' 40.00 'OT' 10.00
and so on... Is there a way to query the data to create a dataset like this?
Thanks,
Ernst.
