Query Criteria Access 2007

[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:

MONTH__________Sum of WORKING HOURS_________________Sum of
TRAVEL_______________Sum of TIME OFF____________Sum of DAY OFF

3________________160 (work+travel+time off+day off for March)____3 (total
of travels during March)____0 (total of TO during March)_____0 (total of DO
during March)
4________________160 ( " during April)_________________________12 ( " during
April)_____________8 ( " during April)______________8 ( " during April)
..
..

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.

 

[Ken Sheridan]

To sum the hours conditionally you'd sum the hours multiplied by the result
of an expression which returns 0 or 1 depending on the value in the
Description column, e.g. to sum the work hours you'd put something like this
in the 'field' row of a blank column in query design view:

[Sum of working hours]: SUM([Working Hours] * IIF([Description] = "Work",1,0))

How you group the results by month depends on whether your date column is of
data/time or text data type. Judging by your sample data its probably text
so you'd group the query by the following expression, which returns
everything to the left of the comma, i.e. the month name, from the string:

LEFT([Date],INSTR([Date],",")-1)

This assumes that the data does not cover the same month in different years
of course.

BTW I'd recommend that you avoid Date as a column name as it’s the name of a
built in function. Something like WorkDate would be better and avoid any
possible confusion.

Ken Sheridan
Stafford, England