Is it possible? Main report / subreport

[LD1010]

I have been asked by our Payroll Clerk if it would be possible to combine two reports she uses each pay periord (2 wks.) into a main report and subreport. I can't see a way to do this but I thought I would try some greater minds than mine.

The first report pulls labor hours from a JobCost Db for a specified group of employees for the specified time periord. The second report pulls time taken off by this same group of employees (vacations, ect.) from a Db called DaysOff for the same specified time period. The only common link between the two is the employee, more specifically the employee number.

Following is the SQL for labor report:

SELECT JobCost.JobCostID, JobCost.Date, JobCost.FileNumber, qryCustomerName.CustomerName, JobCost.CostType, Employees.JobTitle, JobCost.EmployeeNumber, Employees.LastName & ", " & Employees.GoesBy AS EmployeeName, JobCost.LaborDate, JobCost.LaborHours, JobCost.RegTimeHours, JobCost.OTHours, JobCost.DTHours, JobCost.[4-10Hours], JobCost.ESHours, JobCost.ESHoliday, JobCost.ESHolidayWE, IIf([ESHours]=Yes And [ESHoliday]=No And [ESHolidayWE]=No And [LaborHours]>0,100,0) AS ESTag, IIf(([ESHours]=No And [ESHoliday]=Yes Or [ESHolidayWE]=Yes And [LaborHours]>0),150,0) AS ESTagH, JobCost.AddedPayTypeID, TblAddedPay.AddedPayType, TblAddedPay.AddedPayAmount, TblAddedPay.AddedHrRateAmount, IIf(JobCost.AddedPayTypeID>0,[addedPayAmount]+(([RegTimeHours]+[OTHours]+[DTHours])*[AddedHrRateAmount]),0) AS TotAddedPay, [EsTag]+[ESTagH] AS TotESPay, qryMaxPayRates.LastOfHourlyPayRate, IIf([ESHours]=Yes Or [ESHolidayWE]=Yes And [LaborHours]>2 And [LaborHours]<=12,[LaborHours]-2,IIf([ESHours]=Yes Or [ESHolidayWE]=Yes And [LaborHours]>12,10,0)) AS EsOTHours, IIf([ESHours]=Yes Or [ESHoliday]=Yes Or [ESHolidayWE]=Yes And [LaborHours]>12,([LaborHours]-[EsOTHours])-2,0) AS EsDTHours, IIf([EsOTHours]>0 Or [EsDTHours]>0,0,[RegTimeHours]) AS PRTHours, IIf([ESHours]=Yes Or [ESHolidayWE]=Yes And [EsOTHours]>0,[EsOTHours],IIf([ESHoliday]=Yes,0,[OTHours])) AS POTHours, IIf([EsDTHours]>0,[EsDTHours],[DTHours]) AS PDTHours, IIf(JobCost.AddedPayTypeID>0,[AddedPayType],IIf([EsHours]=Yes,"Emergency Service",IIf([ESHoliday]=Yes,"Emergency Service - Holiday",IIf([ESHolidayWE]=Yes,"Emergency Service - Holiday WE","")))) AS AddedPayDesc
FROM (((Employees INNER JOIN JobCost ON Employees.EmployeeNumber = JobCost.EmployeeNumber) LEFT JOIN TblAddedPay ON JobCost.AddedPayTypeID = TblAddedPay.AddedPayTypeID) INNER JOIN qryMaxPayRates ON Employees.EmployeeNumber = qryMaxPayRates.EmployeeNumber) LEFT JOIN qryCustomerName ON JobCost.JobCostID = qryCustomerName.JobCostID
WHERE (((Employees.JobTitle)="Carpenter" Or (Employees.JobTitle)="Construction Supervisor" Or (Employees.JobTitle)="Demo" Or (Employees.JobTitle)="General Laborer" Or (Employees.JobTitle)="Painter" Or (Employees.JobTitle)="Production Manager" Or (Employees.JobTitle)="Project Manager") AND ((JobCost.LaborDate) Between [forms]![frmReportDates1].[cboStartDate] And [forms]![frmReportDates1].[cboEndDate]) AND ((JobCost.LaborHours)>0));

This is the SQL for the time off report:

SELECT QryDaysOffEntry.DaysOffID, QryDaysOffEntry.EmployeeNumber, QryDaysOffEntry.EmployeeName3, QryDaysOffEntry.EmployeeName2, QryDaysOffEntry.FirstDayOff, QryDaysOffEntry.ReturningOn, QryDaysOffEntry.LastDayOff, QryDaysOffEntry.DaysOffTypeID, tblDaysOffType.DaysOffType, QryDaysOffEntry.MemoDaysOff, QryDaysOffEntry.Departments, Employees.JobTitle, QryDaysOffEntry.DaysOffCount
FROM tblDaysOffType INNER JOIN ((Departments INNER JOIN Employees ON Departments.DepartmentID = Employees.Department) INNER JOIN QryDaysOffEntry ON Employees.EmployeeNumber = QryDaysOffEntry.EmployeeNumber) ON tblDaysOffType.DaysOffTypeID = QryDaysOffEntry.DaysOffTypeID
WHERE (((QryDaysOffEntry.FirstDayOff)>=[Forms]![frmReportDates1].[cboStartDate]) AND ((QryDaysOffEntry.ReturningOn)<=[forms]![frmReportDates1].[cboEndDate]) AND ((Employees.JobTitle)="Carpenter" Or (Employees.JobTitle)="Construction Supervisor" Or (Employees.JobTitle)="Demo" Or (Employees.JobTitle)="General Laborer" Or (Employees.JobTitle)="Painter" Or (Employees.JobTitle)="Production Manager" Or (Employees.JobTitle)="Project Manager"))
ORDER BY QryDaysOffEntry.EmployeeName3, QryDaysOffEntry.FirstDayOff;

What she would like to achieve is to have the labor report, which is grouped by EmployeeNumber, to show a subreport in the EmployeeNumber footer that shows all the detailed time off for each employee.

 

[PWise]

What is the problem this is a regular report\subreport

 

[LD1010]

Thanks PWise for taking the time to read my post.

I guess this must have been an ID ten T error. I tried doing this several time yesterday, and although I was getting no error messages, none of the detailed records were showing up in the subreport as expected. In frustration I dumped what I tried yesterday so I can't go back to see what I was doing wrong. But following your reply I tried it again and the subreport worked just fine.

Thanks again for taking the time to reply!!