CODEWITHSUNDEEP
sqldatems-accessselect
szab.kel
szab.kel

Reputation: 2536

MS Access selecting by year intervals

I have a table, where every row has its own date (year of purchase), I should select the purchases grouped into year intervals.

Example:

Zetor 1993
Zetor 1993
JOHN DEERE 2001
JOHN DEERE 2001
JOHN DEERE 2001

Means I have 2 zetor purchase in 1993 and 3 john deere purchase in 2001. I should select the count of the pruchases grouped into these year intervals:

<=1959
1960-1969
1970-1979
1980-1989
1990-1994
1995-1999
2000-2004
2004-2009
2010-2013

I have no idea how should I do this. The result should look like this on the example above:

<=1959
1960-1969 0
1970-1979 0
1980-1989 0
1990-1994 2
1995-1999 0
2000-2004 3
2004-2009 0
2010-2013 0

Upvotes: 0

Views: 68

Answers (2)

Fionnuala
Fionnuala

Reputation: 91356

You may wish to consider Partition for future use:

SELECT Partition([Year],1960,2014,10) AS [Group], Count(Stock.Year) AS CountOfYear
FROM Stock
GROUP BY Partition([Year],1960,2014,10)

Input:

Tractor Year
Zetor   1993
Zetor   1993
JOHN DEERE  2001
JOHN DEERE  2001
JOHN DEERE  2001
Pre 59  1945
1960    1960

Result:

Group   CountOfYear
    :1959   1
1960:1969   1
1990:1999   2
2000:2009   3

Reference: http://office.microsoft.com/en-ie/access-help/partition-function-HA001228892.aspx

Upvotes: 2

4dmonster
4dmonster

Reputation: 3031

Create table with intervals:

tblRanges([RangeName],[Begins],[Ends])

Populate it with your intervals

Use GROUP BY with your table tblPurchases([Item],YearOfDeal):

SELECT tblRanges.RangeName, Count(tblPurchases.YearOfDeal) 
FROM tblRanges INNER JOIN tblPurchases ON (tblRanges.Begins <= tblPurchases.Year) AND (tblRanges.Ends >= tblPurchases.YearOfDeal)
GROUP BY tblRanges.RangeName;

Upvotes: 2

Related Questions