Query Criteria Access 2007 Help please

[Art Vandaley]

Hi,

I have a table with following data:

DATE_______WORKING HOURS_________DESCRIPTION

MARCH, 1___________8_________________WORK
MARCH, 2___________8_________________WORK
MARCH, 3___________5_________________WORK
MARCH, 3___________3_________________TRAVEL
..
..
..
MARCH, 28__________8_________________TRAVEL
MARCH, 29__________7_________________WORK
MARCH, 29__________1_________________TRAVEL
MARCH, 30__________8_________________WORK
MARCH, 31__________8_________________WORK
APRIL, 3_____________8_________________WORK
APRIL, 4_____________8_________________TIME OFF
APRIL, 5_____________8_________________WORK
APRIL, 6_____________5_________________WORK
APRIL, 6_____________3_________________TRAVEL
APRIL, 7_____________8_________________DAY OFF
..
..
..

I want a qurey do like below:


Column1___Column2____________Column3___Column4____Column5
MONTH___WORKING HOURS__TRAVEL__TIME OFF__DAY OFF

3_________160________________3_________0_________0
4_________160________________12________8_________8
..
..
Column2= monthly sum of WORK+TRAVEL+TIME OFF+DAY OFF
Column3= monthly sum of TRAVEL
Column4= monthly sum of TIME OFF
Column5= monthly sum of DAY OFF




In my query, every row is sum of entire month. First and Second columns are
not problem. But I need to seperate travel, time off and day off from
working hours and make their own columns with their montly totals. This is
not easy to do it for me because I try to set criteria which will use
DESCRIPTION column of table to seperate travel, time off and day off hours
as a criteria. I do not know to set suitable criteria for this purpose. Or,
what can I do if setting criteria is not possible?

Thanks for any help in advance.

 

[Steve Schapel]

Art,

Please have a look at Crosstab Query as a way of achieving this.

The specifics will depend, among other things, on whether the date field
is a date/time data type... it looks from your example that it may mot be?

 

[Ken Sheridan]

To conditionally aggregate the values you can sum the value multiplied by the
return value of an expression which evaluates to 0 or 1, e.g.

SELECT
FORMAT([Date],"m") AS [Month],
SUM([Working Hours] AS [Total Hours],
SUM([Working Hours] * IIF([Description] = "Travel",1,0)) AS [Travel],
SUM([Working Hours] * IIF([Description] = "Time Off",1,0)) AS [Time Off],
SUM([Working Hours] * IIF([Description] = "Day Off",1,0)) AS [Day Off]
GROUP BY FORMAT([Date],"m");

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

Doh! Missed the FROM clause:

SELECT
FORMAT([Date],"m") AS [Month],
SUM([Working Hours] AS [Total Hours],
SUM([Working Hours] * IIF([Description] = "Travel",1,0)) AS [Travel],
SUM([Working Hours] * IIF([Description] = "Time Off",1,0)) AS [Time Off],
SUM([Working Hours] * IIF([Description] = "Day Off",1,0)) AS [Day Off]
FROM [YourTableNameGoesHere]
GROUP BY FORMAT([Date],"m");

Ken Sheridan
Stafford, England