Help with a query...

[woneal]

New to the boad, and need help with running this query.

On the TABLE I have 2 fields to be concerned with


DATE RECEIVED and DATE CLOSED
10/11/06 10/15/06
9/12/06 9/13/06
10/16/06 10/18/06
9/10/06 9/19/06

I need to run a query that can tell me how many items were closed between. .

1-5days= Amount closed between that date range.
6-10days= Amount closed between that date range
<11 = any thing closed 11 days or longer.

So the end rusult might look something like this..

1-5 days 3
6-10 days 1
<11 days 0



I will need to run this query on a weekly basis... Also I have only completed Access level II.... So if you could use layman's terms to help me that would be great. .

Thanks in advance,

Craig

 

[VicRauch]

Craig,
I would do it with two (or three) queries. Here is the first query.

SELECT DateText.DateReceived, DateText.DateClosed, DateDiff("d",[DateReceived],[DateClosed]) AS DateRange
FROM DateText;

This query will give you the difference between the two dates, which the next query will put into "date categories" that you are looking for

SELECT IIf(([DateRange]>11),"3 >11",IIf(([DateRange]>=6),"2 6-10","1 1-5")) AS DateCategory, Count(([DateRange])) AS Expr1
FROM Query19
GROUP BY IIf(([DateRange]>11),"3 >11",IIf(([DateRange]>=6),"2 6-10","1 1-5"));

You will notice when you run these two queries that the DateCategories are "1 1-5", "2 6-10", and "3 >11". Each category starts with a 1, 2, or 3 which I put in to get them to sort correctly. Of course I would suggest to remove these "sorting" numbers before presenting the data.
HTH,

 

[woneal]

Gotcha...

Thanks a bunch Vic..