Query With Ranges in output?

  • Thread starter Thread starter Sydneyej
  • Start date Start date
S

Sydneyej

Hello!

I am a novice writer of Access Queries and I'm having trouble figuring
out how to do what I want with one query instead of three. Actually,
this will be easier to explain with the example. Okay, here are my
fields:

Patient ID
Delivery Type
Depression Score

Patient ID is a unique ID of four digits.
Delivery Type is either a 0 (c-section), 1 (vaginal) or 9 (missing).
Depression Score is a whole number between 0 and 30, or null.

I have about 2,000 records. I would like to write a query that spits
out the following:

Delivery Type Dep. Score 0-8 Dep. Score 9-13 Dep. Score
=15
Click to expand...
0 200
100 10
1 1,000
550 50
9 50
40 0

I can get it to do each range, one at a time (e.g. Count Patient ID,
Group By Delivery Type, Where Depression Score <9), but not all at
once. I'm sure there is a super easy way to make this work, but I'm
not sure how to ask the question. Any help would be greatly
appreciated!

Thank you!
Sydney
 
Try this substituting your table name --
SELECT Delivery.[Delivery Type], Sum(IIf([Depression Score]<9,1,0)) AS [Dep
Score 0-8], Sum(IIf([Depression Score] Between 9 And 14,1,0)) AS [Dep Score
9-14], Sum(IIf([Depression Score]>=15,1,0)) AS [Dep Score >=15]
FROM Delivery
GROUP BY Delivery.[Delivery Type];
 
Thank you thank you! This works super well! This is what I ended up
with, mixing your SQL with Access' query building interface:

SELECT tbl_dep.Delivery, Sum(IIf([Edinburgh]<9,1,0)) AS [Edinburgh
<9], Sum(IIf([Edinburgh] Between 9 And 14,1,0)) AS [Edinburgh 9-14],
Sum(IIf([Edinburgh]>=15,1,0)) AS [Edinburgh >14], Sum(IIf([Edinburgh]
Is Null,1,0)) AS [Edinburgh Null]
FROM tbl_dep
WHERE (((tbl_dep.[Pt ID])>1161) AND ((tbl_dep.[Non-English
Speaker])=No))
GROUP BY tbl_dep.Delivery;

If this project has taught me anything it's that the class I need to
take first isn't an Access class, it's a "how to ask questions about
Access" class. This group has helped me so many times when I couldn't
think of how to ask my question usefully to look the answer up in a
book or on a website. You have saved me loads of time! Thanks very
much!

Sydney
 
Back
Top