Group by Years

[zyus]

I have date field named aprdt and my records from 01-jan-98 till todate

In my query i want to group aprdt by YEARS as per format below

1) <2006
2) 2007
3) 2008
4) 2009
5) and so on

How to do the grouping in query

 

[Steve Schapel]

Zyus,

In the Field row of a blank column in the query design grid, enter a
calculated field like this:
YearGroup: IIf([aprdt]<#1/1/2007#,"<2006",Year([aprdt]))

.... and then apply your Group By to this field.

 

[zyus]

Thanks Steve,

Another pop up issue, how to deal with null value in aprdt

Zyus,

In the Field row of a blank column in the query design grid, enter a
calculated field like this:
YearGroup: IIf([aprdt]<#1/1/2007#,"<2006",Year([aprdt]))

.... and then apply your Group By to this field.

--
Steve Schapel, Microsoft Access MVP

I have date field named aprdt and my records from 01-jan-98 till todate

In my query i want to group aprdt by YEARS as per format below

1) <2006
2) 2007
3) 2008
4) 2009
5) and so on

How to do the grouping in query

 

[James A. Fortune]

I have date field named aprdt and my records from 01-jan-98 till todate

In my query i want to group aprdt by YEARS as per format below

1) <2006
2) 2007
3) 2008
4) 2009
5) and so on

How to do the grouping in query

Here's one way:

tblYearDates
YDID AutoNumber
aprdt Date/Time
Data Double
YDID aprdt Data
1 2/2/1998 1
2 3/3/1999 1.5
3 4/4/2000 2.3
4 5/5/2001 3.4
5 6/6/2002 4.5
6 7/7/2003 5.6
7 8/8/2004 6.7
8 9/9/2005 7.8
9 1/1/2006 8.9
10 2/2/2007 9.1
11 3/3/2007 10.11
12 4/4/2008 11.12
13 5/5/2008 12.11
14 6/6/2009 13.12
15 7/7/2009 14.13

-----'Begin Module Code-----
Public Function YearGroup(dtX As Date) As String
Select Case Year(dtX)
Case Is <= 2006:
YearGroup = "<= 2006"
Case Else
YearGroup = CStr(Year(dtX))
End Select
End Function
'------End Module Code------

qryYearDates:
SELECT Sum(Data) AS SumOfData, YearGroup(aprdt) AS YGroup
FROM tblYearDates
GROUP BY YearGroup(aprdt);

!qryYearDates:
SumOfData YGroup
41.7 <= 2006
19.21 2007
23.25 2008
27.29 2009

James A. Fortune
(e-mail address removed)

 

[zyus]

Just to add if aprdt is null i want to take another date field named fstrldt

Zyus,

In the Field row of a blank column in the query design grid, enter a
calculated field like this:
YearGroup: IIf([aprdt]<#1/1/2007#,"<2006",Year([aprdt]))

.... and then apply your Group By to this field.

--
Steve Schapel, Microsoft Access MVP

I have date field named aprdt and my records from 01-jan-98 till todate

In my query i want to group aprdt by YEARS as per format below

1) <2006
2) 2007
3) 2008
4) 2009
5) and so on

How to do the grouping in query

 

[John W. Vinson]

Just to add if aprdt is null i want to take another date field named fstrldt

Year(NZ([aprdt], [fstrldt]))

will use aprdt if it exists, and fstrldt if it doesn't.