Major Problem with Report

[Melissa]

Hi there



I have a table for "tblshifts", which has "ShiftId" as the primary key, and
the fields are: "Employee", "ShiftDate", NoOfShiftsNormal",
"NoOfShiftsSunday", "TotalShifts", etc. Then I have another table for
"tblPayroll", - primary key "PayrollId"; fields include deductions (UIF,
Pension, loans), and also "ShiftId" related to the tblShifts table.



Each time I create a new "Payslip" in tblPayroll, a new line (record) is
added in the tblPayroll table with a new "PayrollId", but then , the same
"ShiftId" appears more than once if those various shifts occurred within the
same time period (in the parameter query). Therefore on my "rptPayslip", I
have a textbox which calculated the "TotalNoOfShifts" worked, and because
the tblPayroll shows the same ShiftId more than once, when the report
calculates the Sum of "TotalNoOfShifts", it adds the same shift more than
once - which therefore results in incorrect calculations.



Here is a detail of my "qryPayroll" query for my "rptPayslip":



(ShiftId) (CompanyId) (tblShiftsEmployeeId) (Client)
(Date) (NoOfShiftsNormal) (PayrollId)

19 8 LESLIE ABC
01/01/2009 1 21

19 8 LESLIE ABC
01/01/2009 1 22

19 8 LESLIE ABC
01/01/2009 1 23



I did not include all the fields from my query, just enough to show that
each time a new PayrollId is created for a new payslip, the same ShiftId
comes up, therefore interfering with the "TotalShiftsWorked" total on the
report: "rptPayslip".



Any advise would be greatly appreciated. Please advise if you require any
further details



Thank you



Melissa

 

[Mark A. Sam]

Melissa,

Remove the PayrolID field from the query. I think that will solve it. The
employee below has multiple payslips with three seperate PayrollID values,
thus giving you a record for each value.

God Bless,

Mark A. Sam

 

[Melissa]

Mark,

Thank you very much for your reply. By removing the payrollid from the
query, will it still create an extra shift each time a new payslip is
created?

Basically to put a very complicated question simpler, this is what I need to
do:

1. go into the "frmcompany", add shifts into the subform, each shift has a
new ShiftId, and then , NoOfShiftsNormal, NoOfShiftsSunday, ShiftDate, etc
THis is how I create all the shifts for each employee (via the company form)

2. at month end (or sometimes the 15th) of each month, I go to the employee
form, and go to the "frmPayroll" on the Payroll Page (tab control) of the
employee form, then I select, NewPayslip. A new payrollId is then
generated, and i type in UIF, loan, and other deductions. Then i need to be
able to select a time period, ie: from 01.01.09 to 31.01.09.

3. click "PreviewPayslip", and then a payslip needs to be generated for the
shifts within that date range, and with the deductions on the payroll form
(uif, etc)

any suggestions?

I have designed the payslip, but it is not limiting the "shifts" by the
specific date range, and I did create a parameter query asking for "between
which date and which date"

also, each time i print a specific payslip for an employee, an extra shift
is added.

thank you kindly for your assistance

melissa

 

[Mark A. Sam]

Melissa,

I can't answer your questions, becuase I don't really understand what you
are doing. It would take seeing the app and knowing the process for me to
help you. This is beyond the scope of the forum. Maybe someone else can
grasp the problem better than I can.

God Bless,

Mark