Grouping Data based on Dates in Access

[singhals]

I have hundereds of rows of data which goes something like this

Date Value
12/31/2009 52
1/1/2009 455
1/1/2005 497
1/1/2009 791
1/1/2009 1357
12/31/2009 68136
1/1/1995 8061
1/1/1995 720
1/1/1996 420

Is there some query I can run which will group this data in 3 groups like say:

Range Totals of Range
1/1/2008-12/31/2009
1/1/2000-12/31/2007
Pre 12/31/1999

Thanks,
singhal

[jimatqsi]

Try this:

SELECT IIf(Year([dtmDate])<2000,"B4Y2K",IIf(Year([dtmDate])<2008,"B4Y08",IIf(Year([dtmDate])<2010,"Y0809","Others"))) AS TimePeriod, tblData.lngValue
FROM tblData;

Substitute your own table name and choice of labels for each time period.

Jim

[NeoPa]

I will assume your first grouping really means up to today, rather than a spec which stops at 31 Dec 2009.
You could GROUP BY a formula similar to :

Switch([DateValue]>#12/31/2007#,1,[DateValue]>#12/31/1999#,2,True,3)

[jimatqsi]

Switch in place of iif

Oh, great, I don't know about Switch, or it fell from my brain due to lack of use. Seems to me iif is said to be a slow instruction, maybe I could substitute this for iif in a lot of places I've used it.

Jim

[NeoPa]

IIf() is often used when Switch() or Choose() are more appropriate. That doesn't mean IIf() isn't really useful, just that others can be more so in some situations.