query for month ranges 1

[mcalvanelli]

I have a report that requires me to show how many cases are 0-6 months old, 7-12 months old, etc., up to 36+ months old. I currently am calculating by hand, but would prefer to just run a report. Is there any way to do this by query? I do not know SQL or VBA, so I am limited in how I can set this up. I did a search for a previous thread on this, but was unable to locate anything.

Thanks in advance.

Melissa Calvanelli
Assistant Court Administrator
Cumberland County, PA

http://www.ccpa.net

 

[Golom]

I do not know SQL or VBA ...
That's pretty much all there is to doing anything in Access. The solution to your problem will have to be one or the other.


You need to run an SQL statement something like

Select

   Partition (Month([SomeDate]), 0, 36, 6) As Interval,

   Count(*) As CaseCount

From myTable

Group By Partition (Month([SomeDate]), 0, 36, 6)

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[mcalvanelli]

Thanks Golom, I guess I should rephrase. I don't know how to write SQL, but I sometimes am able to "steal" code and modify it for what I need.

I might be able to get this to work, but where do I put it? Do I create a query and then switch to SQL and paste this in?

Melissa Calvanelli
Assistant Court Administrator
Cumberland County, PA

http://www.ccpa.net

 

[Golom]

Yes. Paste it into the SQL view of a query ... but change Month([SomeDate]) to the field or calculation that contains your month count and change myTable to the correct table name.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[mcalvanelli]

Ok, I've sort of got it working, except it is only looking at month, so I'm only getting up to 12 months aging. It isn't looking at the year to give me the cases that are pending longer. Is there a way to make that look at year and month for aging?

Thank you again.

Melissa Calvanelli
Assistant Court Administrator
Cumberland County, PA

http://www.ccpa.net

 

[Golom]

You could use something like

DateDiff ( "m", [SomeDate], Date() )

as the first argument to Partition.

That should compute the number of months before today.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[mcalvanelli]

Where do I insert that into my statement? I've tried several and it's giving me an error every time.

Here is what I have:

SELECT Partition (Month([Praecipe Date]),1,60,6) AS [Interval], Count(*) AS CaseCount
FROM CivilTrials
GROUP BY Partition(Month([Praecipe Date]),1,60,6)
DateDiff ( "m", [Praecipe Date], Date() )



Melissa Calvanelli
Assistant Court Administrator
Cumberland County, PA

http://www.ccpa.net

 

[Golom]

SELECT 
Partition ([COLOR=red]DateDiff ("m",[Praecipe Date],Date() )[/color] ,1,60,6) AS [Interval], 
Count(*) AS CaseCount

FROM CivilTrials

GROUP BY Partition([COLOR=red]DateDiff ("m",[Praecipe Date],Date() )[/color],1,60,6)

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[mcalvanelli]

That is perfect!! Thank you so much. This will save me so much time each month. And thank you for being patient with me.

Melissa Calvanelli
Assistant Court Administrator
Cumberland County, PA

http://www.ccpa.net