Periods that span quarters

[sspayne]

Some advice please for an Access novice. I have a report that calculates the total number of days that employees spend on sick leave grouped by quarter (based on first day off sick). I need total sick days per quarter but have trouble when an employee's sick leave spans more than one quarter - the entire period of sick leave gets thrown into the quarter when the sick leave started. How can I work it so that the sick leave is allocated into the appropriate quarters rather then just the one it started in? All advice appreciated.

 

[Blorf]

is each sick day listed seperately, like
Mary Jane 1/15/03
Mary Jane 1/16/03?

If so, then you can group and sum by quarter by using an expression like this

Qtr: Format([date],"yy-q&quot

For 1/15/03 this will return 03-1.

Hope that helps.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[sspayne]

Thanks ChaZ. At the moment I just have start date and end date, and a datediff query works out the total number of days off sick. I would like to use your approach - is there a way around this? Thanks again.

 

[Blorf]

You can use the same format as above, but will need to include a count of days as well. The only danger is that the start date may be in 1 quarter, and the second may be in a different quarter.

Only way around this that I know of is to use some VBA coding. Are you familiar?

ChaZ

Ascii dumb question, get a dumb Ansi

 

[sspayne]

Not really! I know what it is and have pasted other people's scripts into by db before, but certainly not an expert at writing VBA from scratch. I think if someone could tell me if there is a way of listing all dates between a start and end date then I could make use of your original suggestion.