NEED HELP - AGING STATISTICS 1

[proflola]

Here's the scenario:

I need to count cases open at the end of each week that are <=30 days old and also the cases that are >30 days old. I've developed two queries that use a parameter to allow the user to enter the End Date. Here's a sample of one:

SELECT Count(case_tracking_tab.case_no) AS [30 Days or Less]
FROM case_tracking_tab
WHERE (((DateDiff("d",[rcvd_dt],[Enter Ending Date]))<=30) AND ((case_tracking_tab.comp_dt) Is Null));

It works fine and I'm able to combine the results of the two queries into a combined query.

**I need help** with the solution to create a query to display a month broken down into W1, W2, W3, W4. It's to be used for a bar chart to the compare the results of each week.

My brain is fried and would appreciate any suggestions...

Thanks!

Sandy

 

[dhookom]

How would you divide months into weeks? Nov 2007 start on the first week as Oct ends in.

You might want to try SQL like the following to get both counts in a single query. I also don't care for parameter prompts in queries.

SELECT 
Sum(Abs(DateDiff("d",[rcvd_dt],[Enter Ending Date])<=30)) AS [30 Days or Less],
Sum(Abs(DateDiff("d",[rcvd_dt],[Enter Ending Date])<=60)) AS [60 Days or Less]
FROM case_tracking_tab
WHERE case_tracking_tab.comp_dt Is Null;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[proflola]

Duane,

Thanks for your quick reply! I'm teaching myself SQL, but I'm still limited with what I can do with it. Your SQL query should really help.

I also developed a form that allows the user to input a WeekEnding date which is used to calculate the previous 3 previous weekdays by subtracting them by 7 and places the results in text boxes on the form. I point to those dates in Wk1,Wk2,Wk3,Wk4 queries. I'm sure there's a simpler way to do it via SQL and Visual Basic.

Your advice was great! Thanks again!

--Sandy