Group Records on Time Intervals

[primagic]

I have a table that contains dates and other information

I want to create a query that will sum the number of records that fall within certain time periods based on that date field. Would this need to be display in a cross tab query?

i.e
<1 month
>1 month but <=3 months
>3 months but <=6 months
>6 months but <=9 months
>9 months but <=12 months
>12 months

 

[dhookom]

I would create a small table of month ranges so that you can easily change these when they someone wants different ranges.

[tt]
tblMonthRanges
======================
[blue]MthFrom MthTo MthTitle[/blue]
-9999 0 Under 1
1 3 1 to 3
4 6 4 to 6
7 9 7 to 9
10 12 10 to 12
13 9999 More than 12
[/tt]

You can then use the DateDiff("M",....) to grab the number of months and find the range record. Use the MthTitle as the crosstab Column Heading.

Duane
Hook'D on Access
MS Access MVP

 

[primagic]

I have added a datediff function to the query that gets the months between the datefield and today. So I get the number of months. But I cant see how from the table of ranges that I create, how these records get put into those ranges in a query?

 

[dhookom]

Create a query with your table and tblMonthRanges. Set the criteria under the DateDiff() to:

BETWEEN  MthFrom AND MthTo

Make sure this is a totals/group by query and only display the fields from tblMonthRanges and one Count from your "table that contains dates and other information"

Duane
Hook'D on Access
MS Access MVP

 

[PWise]

Duane
wouldn"t you need a Dateadd query based on the Intervals table

SELECT Intervals .ID, DateAdd("m",[MthFrom],Date()) AS Start, DateAdd("m",-[mthto],Date()) AS Stop
FROM Intervals ;

and

Select MthTitle ,sum(Somefield) as expr1
from Intervals ,sometable
Where Datefield BETWEEN  MthFrom AND MthTo

 

[PWise]

sorry should be

Select id,sum(Somefield) as expr1
from QryDateadd ,sometable
Where Datefield BETWEEN MthFrom AND MthTo
Group by id

 

[PWise]

Select id,sum(Somefield) as expr1
from QryDateadd ,sometable
Where Datefield BETWEEN Start AND Stop
Group by id

 

[PWise]

or you can do an inner join

SELECT Sum(sometable.Somefield) AS SumOfSomefield, Int.ID ,start,stop
FROM Sessions 
INNER JOIN [Int] 
ON [sometable].[Datefield] <= [int].[start] 
And [sometable].[Datefield] > [int].[stop]
GROUP BY Int.ID, Start,stop;

 

[primagic]

When I create the query:

SELECT tblMonthRanges.RangeID, DateAdd("m",[MthFrom],Date()) AS Start, DateAdd("m",-[mthto],Date()) AS Stop
FROM tblMonthRanges;

I get the following results:
Start Stop
31/12/1177 31/03/2011
30/04/2011 31/12/2010 <----- Is this date not wrong
31/07/2011 30/09/2010 <----- Is this date not wrong
31/10/2011 30/06/2010 <----- Is this date not wrong
31/01/2012 31/03/2010 <----- Is this date not wrong
30/04/2012 31/12/1177

They dont look like they are in sequential order? First row looks fine, but then i would expect the next row to be 30/04/2011 - 31/07/2011

 

[PWise]

Sorry what you need is a minus - by the start

DateAdd("m",-[MthFrom],Date()) AS Start

or you might have play around a bit with you ranges

 

[PWise]

my ranges are
f1 f2
0 1
2 3
4 6
7 12
12 9999

and my query

is

DateAdd("m",-[f1],Date()) AS Start, DateAdd("m",-[f2],Date()) AS Stop

 

[PWise]

Sorry my date are worng but the basic method is
1) create an intrval table
2) cerate a query that with dateadd that will give you date range
3)create a query that will join your table to the daterange query

4)group on the Qrydateadd id field and sum(somefield)

 

[dhookom]

I'm waiting for primagic to share the names of
"table that contains dates and other information"
and
"that date field"

I'm not sure if they are secret or what ;-)

Duane
Hook'D on Access
MS Access MVP

 

[primagic]

Ok here you go

my table with the dates and other information is called

"Info"

The date field in that table is called: "Field23"

Its and imported table. So NO i didnt name my fields like that

 

[dhookom]

This depends on the Field23 being either before or after Date(). This SQL is for before Field23:

SELECT RangeID, [MthFrom],[mthto],Count(*) as NumOf
FROM tblMonthRanges, Info
WHERE DateDiff("M",Field23,Date()) Between [MthFrom] AND [mthto]
GROUP RangeID, [MthFrom],[mthto];

Duane
Hook'D on Access
MS Access MVP

 

[primagic]

Field23 will be after date()

 

[primagic]

I get a syntax error on the GroupBy clause in your code:

SELECT RangeID, [MthFrom],[mthto],Count(*) as NumOf
FROM tblMonthRanges, Info
WHERE DateDiff("M",Field23,Date()) Between [MthFrom] AND [mthto]
GROUP RangeID, [MthFrom],[mthto];

 

[primagic]

I see it. missing 'BY'