Work tracking query

[EricK]

Suppose I have a table with fields as follows:
Case_id Date_in Date_out Case_type

Case_id is primary key: 1,2,3 etc
Date_in was the date the case came in. This is always present.
Date_out is the date the case was completed. This will be blank if the case
has not yet been completed.
Case_type will be one of type1, type2, type3 etc

Given two dates, date1 and date2, which need to be user specified each time
this is run, I want to produce output which looks like this:
BF In 0-1 2-3 4-5 >5 CF
type1
type2 # of cases
type3

i.e. each row relates to a particular case type.
"BF", Brought Forward, is the number of cases of that type which had been
received before date1 but had not been returned by then (so date_in will be
<date1 and date_out will be Null or >=date1).
"In" is the number of cases which came in between date1 and date2
"0-1" is the number of cases which went out in between date1 and date 2 and
took either 0 or 1 days.
"2-3" is the number of cases which went out between date1 and date2 and took
2 or 3 days.
Similarly for "4-5" and ">5". Note for all of these it doesn't matter when
the case came in, only that it went out between the two dates
"CF", Carry Forward, is the number of cases which had not been returned by
date2 (so date_out will be Null or will be greater than date2).

How do I go about doing this?

Thanks for any help,

Eric

 

[Jerry Whittle]

You need a crosstab query. That will actually be the easy part.

But first you need a query that will output records that look like:

type1 BF
type2 In
type2 0-1
type3 2-3
type1 CF

This is either going to take a rather complicated Case statement in code or
a combination of queries unioned together or an IIf statement from heck.

Also it looks to me that you might be counting the same record more than
once. The criteria for a BF and CF looks about the same.

Too bad I'm somewhat busy at work this week as this would be an interesting
challenge.

 

[EricK]

Thanks Jerry,

Some records may be counted more than once (eg a case which was already
there at the start and not yet out by the end will appear in both BF and CF).
I don't know whether I have got my definitions of BF and CF right, but the
relationship which should hold is

"BF"+"In" - "0-1" - "2-3" - "4-5" - ">5" = "CF"

i.e. During the relevant time interval the number of cases which were there
at the start plus the number which came in during that interval, less the
amount which went out (in however many days) should equal the amount of cases
which were carried forward.

To make the problem a little harder(!), I forgot to mention that ideally the
number of days taken should discount weekends.

Eric