Help with counting Query

[metalhead22]

Hello --- I have a database that has several fields including three date fields. I want to count the number of records in relationship to each date field grouped by year. This is my existing SQL statement, but it isn't counting everything correctly - mainly 2surg and 3surg. For example, it left off two records that did have a 2surg date, but they were not the same year as the 1surg date. Hope I am making sense. Any help is appreciate.



Steve

SELECT (Year([Surgery Date])) AS [Year], Count((Year([Surgery Date]))) AS 1Surg, Count((Year([Post op 2 date]))) AS 2Surg, Count((Year([Post op 3 date]))) AS 3Surg, ([1surg]+[2surg]+[3surg]) AS Total
FROM [Tumor Data]
GROUP BY (Year([Surgery Date]));

[thhui]

(Group by) must be matched by count(Group)
Therefore, you must separate your queries into 3 parts

SELECT (Year([Surgery Date])) AS [Year] , Count((Year([Surgery Date]))) AS 1Surg
FROM [Tumor Data]
GROUP BY (Year([Surgery Date])); 
 
SELECT (Year([Post op 2 date]) AS [Year], Count((Year([Post op 2 date]))) AS 2Surg, 
FROM [Tumor Data]
GROUP BY (Year([Post op 2 date])); 
 
SELECT (Year([Post op 3 date]) AS [Year], Count((Year([Post op 3 date]))) AS 3Surg, 
FROM [Tumor Data]
GROUP BY (Year([Post op 3 date]));

[metalhead22]

Can this be put in one MS Access Query? I put it in and when I try to run it says "Characters found after end of SQL statement". Pressing the help button on this messages states that there can't be characters after the semi colon.

Thanks

Steve

[thhui]

I mean separate queries.

Not one query.

So, 3 queries instead of one.

PS: ; is the end of sql statement query character

[metalhead22]

Right --- so is there a way to combine these select statements into one query and give a totals grouped by year --- so that the output would be similar to:

Year 1Surg 2Surg 3Surg Total
2007 20 10 5 35
2008 23 7 6 36

etc...?

Thanks

Steve

[thhui]

Try to use union to combine 3 separate queries into one!

[metalhead22]

Tried that - get syntax error --- here is the code:

SELECT (Year([Surgery Date])) AS [Year] , Count((Year([Surgery Date]))) AS 1Surg
FROM [Tumor Data]
GROUP BY (Year([Surgery Date]))
UNION
SELECT (Year([Post op 2 date]) AS [Year], Count((Year([Post op 2 date]))) AS 2Surg,
FROM [Tumor Data]
GROUP BY (Year([Post op 2 date]))
UNION
SELECT (Year([Post op 3 date]) AS [Year], Count((Year([Post op 3 date]))) AS 3Surg,
FROM [Tumor Data]
GROUP BY (Year([Post op 3 date]));

[thhui]

Make a query something like this below:-

SELECT * INTO Dbase FROM 
[SELECT *, 1 as gp
FROM Dbase1
union
SELECT *, 2 as gp
FROM Dbase2
union
SELECT *, 3 as gp
FROM Dbase3];

[tinytree]

that' right "union"