Totals in a report.

[abourg8646 via AccessMonster.com]

I have a report made from a union query. I then used a cross tab query so the
dates would run across the top of the page. everything works correctly except
totals. my question is there a formula that can sum the total and if there is
a field with text in it then it would give a numerical value of zero? My
report will look like this.

1 May 2 May 3 May 4 May .... 31
May Total
JR Time Wkd 06-14 06-14 06-14 14-22 22-06
0
Hour Wkd 8 8 8 8
8 176
Vacation 0 0 0 0
0 0
Ctr Hours 8 8 8 8
8 176
Bob Time Wkd 06-14 06-14 06-14 14-22 22-06
0
Hour Wkd 0 0 8 8
8 160
Vacation 8 8 0 0
0 0
Ctr Hours 8 8 8 8
8 176

The 06-14 is stored as text.

 

[Duane Hookom]

I would use the Monthly crosstab report sample from
http://www.tek-tips.com/faqs.cfm?fid=5466. You should be able to convert the
expressions from monthly columns to daily columns.

 

[abourg8646 via AccessMonster.com]

I've don that. What I'm trinig to do is total each row uder the date. My
problem is one of the rows is text instead of a #. Here is my SQL
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");

Expr1003 is all the data that will be displayed under the date. there is 9
fields in expr1003 and one of them is text.

I would use the Monthly crosstab report sample from
http://www.tek-tips.com/faqs.cfm?fid=5466. You should be able to convert the
expressions from monthly columns to daily columns.

I have a report made from a union query. I then used a cross tab query so the
dates would run across the top of the page. everything works correctly except

[quoted text clipped - 22 lines]

The 06-14 is stored as text.

 

[Duane Hookom]

I don't understand. The Max of MaxOfItem Wkd will be displayed under the
date, not expr1003. What do you mean by "9 fields in expr1003"?

You should take a couple seconds and change expr1003 and Expr1 to something
that makes sense.

--
Duane Hookom
Microsoft Access MVP

I've don that. What I'm trinig to do is total each row uder the date. My
problem is one of the rows is text instead of a #. Here is my SQL
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");

Expr1003 is all the data that will be displayed under the date. there is 9
fields in expr1003 and one of them is text.

I would use the Monthly crosstab report sample from
http://www.tek-tips.com/faqs.cfm?fid=5466. You should be able to convert the
expressions from monthly columns to daily columns.

I have a report made from a union query. I then used a cross tab query so the
dates would run across the top of the page. everything works correctly except

[quoted text clipped - 22 lines]

The 06-14 is stored as text.

 

[abourg8646 via AccessMonster.com]

Duane you wrote I think this can be done by first creating a normalizing
union query of the
Schedule table. Something like:
SELECT EmployeeID, [Date wkd] as DateWkd, [Time Wkd] as TimeWkd,
[Hrs Worked] As Hrs, "Hours Worked" as TimeType
FROM Schedule
UNION ALL
SELECT EmployeeID, [Date wkd] as DateWkd, [Time Wkd] as TimeWkd,
[Contract Hrs], "Contract Hours"
FROM Schedule
UNION ALL
SELECT EmployeeID, [Date wkd] as DateWkd, [Time Wkd] as TimeWkd,
[Hol], "Holiday"
FROM Schedule
UNION ALL
SELECT EmployeeID, [Date wkd] as DateWkd, [Time Wkd] as TimeWkd,
[Vac], "Vacation"
FROM Schedule
--- etc ---
UNION ALL
SELECT EmployeeID, [Date wkd] as DateWkd, [Time Wkd] as TimeWkd,
[O/T], "OT"
FROM Schedule;

You can then create a crosstab from this that uses the Employee info as Row
Headings, DateWkd as column headings, and Sum(Hrs) as the Value.

I'm not sure how to handle the Time Wkd since it looks to be text while
other values are numeric. I would also report the column headings as relative

dates rather than actual dates.

This worked great. Its the Time Wkd that gives me the probem. Expr1003 was
made when I did the union query. It's a list of the items under the date.
Expr1 is a switch so that in my report I can sort in a particular order and
it works. This reference was from 6/30/09 2155 if that helps.

I don't understand. The Max of MaxOfItem Wkd will be displayed under the
date, not expr1003. What do you mean by "9 fields in expr1003"?

You should take a couple seconds and change expr1003 and Expr1 to something
that makes sense.

I've don that. What I'm trinig to do is total each row uder the date. My
problem is one of the rows is text instead of a #. Here is my SQL

[quoted text clipped - 22 lines]

 

[Duane Hookom]

You may need to create two crosstabs; one with the Time Wkd and the other
with the Hrs Wkd. Then use a union query to combine them for your report.

--
Duane Hookom
Microsoft Access MVP

Duane you wrote I think this can be done by first creating a normalizing
union query of the
Schedule table. Something like:
SELECT EmployeeID, [Date wkd] as DateWkd, [Time Wkd] as TimeWkd,
[Hrs Worked] As Hrs, "Hours Worked" as TimeType
FROM Schedule
UNION ALL
SELECT EmployeeID, [Date wkd] as DateWkd, [Time Wkd] as TimeWkd,
[Contract Hrs], "Contract Hours"
FROM Schedule
UNION ALL
SELECT EmployeeID, [Date wkd] as DateWkd, [Time Wkd] as TimeWkd,
[Hol], "Holiday"
FROM Schedule
UNION ALL
SELECT EmployeeID, [Date wkd] as DateWkd, [Time Wkd] as TimeWkd,
[Vac], "Vacation"
FROM Schedule
--- etc ---
UNION ALL
SELECT EmployeeID, [Date wkd] as DateWkd, [Time Wkd] as TimeWkd,
[O/T], "OT"
FROM Schedule;

You can then create a crosstab from this that uses the Employee info as Row
Headings, DateWkd as column headings, and Sum(Hrs) as the Value.

I'm not sure how to handle the Time Wkd since it looks to be text while
other values are numeric. I would also report the column headings as relative

dates rather than actual dates.

This worked great. Its the Time Wkd that gives me the probem. Expr1003 was
made when I did the union query. It's a list of the items under the date.
Expr1 is a switch so that in my report I can sort in a particular order and
it works. This reference was from 6/30/09 2155 if that helps.

I don't understand. The Max of MaxOfItem Wkd will be displayed under the
date, not expr1003. What do you mean by "9 fields in expr1003"?

You should take a couple seconds and change expr1003 and Expr1 to something
that makes sense.

I've don that. What I'm trinig to do is total each row uder the date. My
problem is one of the rows is text instead of a #. Here is my SQL

[quoted text clipped - 22 lines]

The 06-14 is stored as text.