Query With Ranges in output?

[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

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

 

[KARL DEWEY]

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];

 

[Sydneyej]

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