Overtime Report by Month...

[Guest]

Hi there

I need to create a report that shows total overtime by month, type of overtime, by department and Employee. My Query looks like this

Dept Name Date of Overtime Type of Overtime Number of hour

Admin Jill Jan 1, 2004 Regular 2.
IS Joe Jan 19, 2004 SDO
Admin Jill Feb 23, 2004 SDO


I need a report that looks like this

Jan Feb Employee Total
Total OT SDO Regular Total OT SDO Regular Total OT SDO Regula
ADMI
Jill 2.5 2.5 8 8 10.5 8 2.

Dept TOTAL 2.5 2.5 8 8 10.5 8 2.

I
Joe 8 8

Dept TOTAL 8 8


Is this possible?

Thanking in advance!

L

 

[Duane Hookom]

You can create multiple values by combining a crosstab and cartesian query.

Create a sample using the Northwind database to create both a Quantity and
Quantity * Price value for each column.
-Create a new table tblXtabColumns with a single field [FldName]
-add two records to this table
"Quantity"
"QtyXPrice"
-create a crosstab with the table [Products], [Orders], [Order Details], and
[tblXtabColumns]
-join the first three tables as expected but don't join tblXtabColumns to
any other table
-your Column Heading expression is
Expr1:[FldName] & Month([OrderDate])
-your Value expression is
DaVal:IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details]![UnitPrice])
Sum
-I set OrderDate for the first three months of 1998
The full SQL is:

TRANSFORM Sum(IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details]![UnitPrice])) AS DaVal
SELECT Products.ProductName
FROM tblXtabColumns, Orders INNER JOIN (Products INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID =
[Order Details].OrderID
WHERE (((Orders.OrderDate) Between #1/1/1998# And #3/31/1998#))
GROUP BY Products.ProductName
PIVOT [FldName] & Month([OrderDate]);

You will get two columns per month QtyXPriceN and QuantityN.

--
Duane Hookom
MS Access MVP
--

Hi there,

I need to create a report that shows total overtime by month, type of

overtime, by department and Employee. My Query looks like this:

Dept Name Date of Overtime Type of Overtime Number of hours

Admin Jill Jan 1, 2004 Regular 2.5
IS Joe Jan 19, 2004 SDO 8
Admin Jill Feb 23, 2004 SDO 8



I need a report that looks like this:

Jan

Feb Employee Totals

Total OT SDO Regular Total OT

SDO Regular Total OT SDO Regular

ADMIN
Jill 2.5 2.5

8 8 10.5 8
2.5

Dept TOTAL 2.5 2.5 8

8 10.5 8 2.5