Count Records with Formatted Year 1

[awl]

In the SQL below of my query A, qryIncident Master File, the field “[DOIYear]” is formatted as “yyyy” to give me the correct Year. I want another query B to sum the total number of incidents grouped by Year, [DOIYear]. I can’t get the grouped query to work properly; it will group the year and treat is as a “date” and not year. If someone could give a suggestion, it would be great!

Query A:
SELECT [tblIncidents Master File].IncidentRptID, [tblIncidents Master File].DOIncident, [tblIncidents Master File].DOIncident AS DOIYear, [tblIncidents Master File].Incident, [tblIncidents Master File].Section, [tblIncidents Master File].Remarks, Count([tblIncidents Master File].DOIncident) AS [Count]
FROM [tblIncidents Master File]
GROUP BY [tblIncidents Master File].IncidentRptID, [tblIncidents Master File].DOIncident, [tblIncidents Master File].DOIncident, [tblIncidents Master File].Incident, [tblIncidents Master File].Section, [tblIncidents Master File].Remarks;

Query B:
SELECT [qryIncidents Master File].DOIYear, Count([qryIncidents Master File].DOIYear) AS CountOfDOIYear
FROM [qryIncidents Master File]
GROUP BY [qryIncidents Master File].DOIYear;

 

[PHV]

Replace this:
[tblIncidents Master File].DOIncident AS DOIYear
with this:
Year([tblIncidents Master File].DOIncident) AS DOIYear

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

 

[Golom]

If DOIYear is a datetime field then

Query B:

SELECT Year(F.DOIYear) As TheYear, 
       Count(F.DOIYear) AS CountOfDOIYear

FROM [qryIncidents Master File] As F

GROUP BY Year(F.DOIYear);

 

[awl]

Thanks for each of your replies.
PHV: I replaced the contents as you suggested and the end result was that in all the records of Query A, the year stated 1905 and Query B had four groups of 1905 since my data covered years 2002 through 2005.

Golom: I replaced Query B with your suggestions and this is exactly what I was looking for, each of the 4 years were grouped and the total counts for each year.

Thanks very much.

 

[MajP]

So why does this work/not work? Access could care less about your format. So if your displaying the date part, time part, or month part, the data is stored as followed:

The internal structure of a serial number is a double precision number. The integral portion represents the number of days since December 30, 1899 and the fractional portion represents the time as a fraction of a day. Midnight
is the beginning of a day and therefore it's represented by the fraction zero

When you use the year function you return an integer representing a year. Clearly you were only working with dates, vice the examples that converted them to year (integers)