March 27, 2008 at 11:22 pm
I am setting up an ADP project in Access that is attached to an MSDE sql backend. Here is the problem. I have a table that is used to track calculated turn around times for samples that have various status (STAT, ROUTINE and Priority). I group the table according to the priority and then count the TATs in each. The problem is I would like to display the TAT counts with multiple "cutoffs" applied. So I would like one count where TAT is >0 for each priority, then in the next column the count where TAT is <60, then <120, then <260 ect. This is my starting point:
SELECT Field4, COUNT(TATCal) AS TATAll
FROM dbo.tTAT
GROUP BY Field4
HAVING (COUNT(TATCal) > 0)
How do I make TAT60 (my second column) = Count(TATCal) < 60 ect. The final product should be something like:
Priority TATAll TAT60 TAT120 TAT260
STAT 100 40 70 90
ROUTINE 300 260 270 290
You get the idea. Once I figure out how to do the counts I want to do the same thing with calculated averages. Please help!!
March 28, 2008 at 7:41 am
I haven't done Access in years, however, I believe that combining COUNT, IIF and NULL's could work for you. Try something like this:
SELECT Field4
, COUNT(TATCal) AS TATAll
, COUNT(IIF(TATCal<60, 1, Null)) AS TAT60
FROM dbo.tTAT
GROUP BY Field4
HAVING (COUNT(TATCal) > 0)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 28, 2008 at 3:49 pm
Thank you very much, I will give that a try. The other thing I am not sure about is whether to do this with a GROUP BY or a HAVING clause. I will try and adapt your suggestion to a GROUP BY format and see if the output is any different.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply