Report Headers for Flexible Pay Periods

J

Jimmy Domsalla

I'm working on a payroll database for a company the handles their pay-periods
as being the 1st-15th and then 16th to the end of the month. This results in
fluxuating pay period lengths (15 days, 16 days, 15 days, 13 days, 15 days,
etc.).

I've figured out how to produce a cross-tab query that generates the data;
however, I am finding myself with 2 issues.

First, I need to show all of the days, including those days that no one
worked (not really a "need", but the boss would like it better).

Second, I need to generate a report that is flexible enough to handle the
fluxuating lengths of the pay periods.
 
D

Duane Hookom

Does your crosstab produce dynamic or static column headers? Could you share
the SQL view of your crosstab?
 
J

Jimmy Domsalla

Here's the SQL:

TRANSFORM Sum([TimeLog].[Hours]) AS SumOfHours
SELECT [Report:payPeriod:Employee].[Employee],
[Report:payPeriod:payPeriod].[PayPeriod], [Data:payPeriods].[Date Start],
[Data:payPeriods].[Date Finish], [Data-JobTypes].[Job Type],
[TimeLog].[ProjectID], [Projects].[Project Name]
FROM [Report:payPeriod:payPeriod] INNER JOIN [Data:payPeriods] ON
[Report:payPeriod:payPeriod].[PayPeriod]=[Data:payPeriods].[Payperiod
Number], [Report:payPeriod:Employee] INNER JOIN ([TimeCards:All] INNER JOIN
([Data-JobTypes] INNER JOIN ((IGCProjNumb INNER JOIN ([Data:LoggedDates]
INNER JOIN TimeLog ON [Data:LoggedDates].[Date]=[TimeLog].[Date]) ON
[IGCProjNumb].[IGCADProjNumber]=[TimeLog].[ProjectID]) INNER JOIN Projects ON
[IGCProjNumb].[Entry Number]=[Projects].[Entry Number]) ON
[Data-JobTypes].
Code: 
=[Projects].[Type]) ON [TimeCards:All].[Employee
Number]=[TimeLog].[Employee ID]) ON
[Report:PayPeriod:Employee].[Employee]=[TimeCards:All].[Employee]
WHERE ((([Data:LoggedDates].[Date])=[date start])) Or
((([Data:LoggedDates].[Date])>[date start] And
([Data:LoggedDates].[Date])<[date finish])) Or
((([Data:LoggedDates].[Date])=[date finish]))
GROUP BY [Report:PayPeriod:Employee].[Employee],
[Report:PayPeriod:PayPeriod].[PayPeriod], [Data:PayPeriods].[Date Start],
[Data:PayPeriods].[Date Finish], [Data-JobTypes].[Job Type],
[Projects].[Year], [Projects].[Project Number], [TimeLog].[ProjectID],
[Projects].[Project Name]
ORDER BY [Data-JobTypes].[Job Type], [Projects].[Year], [Projects].[Project
Number]
PIVOT [Data:LoggedDates].[Date];
 
D

Duane Hookom

Assuming [Date] is an actual date/time field. I would create a form with a
control where the user would enter or select either the 15th of the month or
the last day of the month (Forms!frmDate!txtEndDate)

Then change the column heading expression from [Date] to
ColHead: "D" & DateDiff("d",[Date],Forms!frmDate!txtEndDate)

Set the Column Headings property to: "D15","D14","D13"..."D0"

D15 will be the sum of hours from 15 days earlier than the value in
Forms!frmDate!txtEndDate. D0 will be the values for
Forms!frmDate!txtEndDate.

You may need to select Query->Parameters and enter:
Forms!frmDate!txtEndDate Date/Time

Your report may want to show dates earlier than you want so you will need to
run code in the report to hide some columns.
 
Top