Pull in 1 month at a time from a crosstab query 1

[abourg]

I have a report that was made from a union query and then put into a crosstab. I follow the tip FAQ but my problem is each month has different # of days. At the far right hand end I will end up with Totals for each row. I'm using D30-D0 as the dates. Here is the SQL Statement:

PARAMETERS Forms!frmDate!txtEndDate DateTime;
TRANSFORM Max([Stats Query].[MaxOfTime Wkd]) AS [MaxOfMaxOfTime Wkd]
SELECT [Stats Query].EmployeeID, [Stats Query].Expr1003
FROM [Stats Query]
GROUP BY [Stats Query].EmployeeID, [Stats Query].Expr1003, [Stats Query].Expr1
ORDER BY [Stats Query].EmployeeID, [Stats Query].Expr1
PIVOT "D" & DateDiff("d",[Datewkd],Forms!frmDate!txtEndDate) In ("D30","D29","D28","D27","D26","D25","D24","D23","D22","D21","D20","D19","D18","D17","D16","D15","D14","D13","D12","D11","D10","D9","D8","D7","D6","D5","D4","D3","D2","D1","D0");

This works for a month with 31 days in it. If I only have 30 days then when I total it will pull in 1 day from the previous month.

 

[dhookom]

You could add a where clause like

PARAMETERS Forms!frmDate!txtEndDate DateTime;
TRANSFORM Max([Stats Query].[MaxOfTime Wkd]) AS [MaxOfMaxOfTime Wkd]
SELECT [Stats Query].EmployeeID, [Stats Query].Expr1003
FROM [Stats Query]
WHERE Month(DateWkd) = Month(Forms!frmDate!txtEndDate)
GROUP BY [Stats Query].EmployeeID, [Stats Query].Expr1003, 
[Stats Query].Expr1
ORDER BY [Stats Query].EmployeeID, [Stats Query].Expr1
PIVOT "D"  & DateDiff("d",[Datewkd],Forms!frmDate!txtEndDate) 
In ("D30","D29","D28","D27","D26","D25","D24","D23","D22","D21"
,"D20","D19","D18","D17","D16","D15","D14","D13","D12","D11"
,"D10","D9","D8","D7","D6","D5","D4","D3","D2","D1","D0");

Duane
Hook'D on Access
MS Access MVP

 

[abourg]

Is there a way to sum the rows in Expr1003. There are 10 rows per name. I need a total of each row for the month.

 

[dhookom]

I'm afraid I don't understand. Do you not want to group by [Expr1003]? What is [Expr1003] and why didn't you give it a descriptive name? Have you tried adding another Row Heading using the Sum?

Duane
Hook'D on Access
MS Access MVP

 

[abourg]

I'm sorry it was MaxOfTime Wkd. When I made the union query then it took the values of 10 fields and placed it under the date. The expr1003 was the names of the fields. My report looks like this.

Name expr1003 d0 d1 d2 ... d29 d30 total
John Time Wkd 06-14 06-4 06-14 06-14
Hrs Wkd 8 8 8 8 24
Vac 0 0 0 0 0
Hol 0 0 0 0 0

D0-D30 are dates and all that works. I'm triing to put a total on the far right so that it will total a whole month of Hrs Wkd,a whole month of Vac ect. per Name.

 

[dhookom]

Did you try add another Row Heading with Sum of [MaxOfTime Wkd]?

Duane
Hook'D on Access
MS Access MVP