SQL COUNT Query w/ three other sub-queries

[ridgerider]

I've got a table with the following fields:
---------------------------------
StudyID Year Month Modality

A222 2006 jan CT
B342 2006 jan CT
D545 2006 mar MR
S676 2006 apr CT
S897 2006 apr MR
R321 2006 apr MR
Q908 2005 mar MR
T456 2005 mar MR
T324 2005 feb CT
S100 2005 feb MR
---------------------------------

I want to return this Query:
-----------------------------------------
Year Month Modality StudyCount

2006 jan CT 2
2006 mar MR 1
2006 apr CT 1
2006 apr MR 2

2005 mar MR 2
2005 feb CT 1
2005 feb MR 1
-----------------------------------------

It's a COUNT Query and I want to count how many studies are there sorted by each Modality, Month, and Year.
All I've got is:

SELECT COUNT(StudyID) AS StudyCount
FROM TableName;

Should I use VBA to do this or could it all be done using one SQL based Query in Access ?

Thanks in advance.

 

[PHV]

A starting point:
SELECT [Year], [Month], Modality, Count(*) AS StudyCount
FROM TableName
GROUP BY [Year], [Month], Modality

I quite don't understand why you need TWO fields for storing a SINGLE DateTime value.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

SELECT Year, Month, Modality, COUNT(*) From TableName GROUP BY Year, Month, Modality order by year, month, modality

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Golom]

Select [Year], [Month], Modality, Count(*) As TheCount

From myTable

Group By [Year], [Month], Modality