Grouping Records Into Date Buckets

[kjb]

Hi All,

I have a table with a 'Required By' date field and I need
to analyse the records in weekly buckets, i.e. count the
number of records week by week over the spread of
the 'Required By' dates. I plan to graph this in Excel.

Any ideas would be very much appreciated.

Kev.

 

[Michel Walsh]

Hi,

We need to generate those missing weeks, not by hand, but by SQL, that is.

I assume you have a table, let call it Iotas, one field, Iota, the pk, with values from 0 to, say
99.

--- untested ---


In a first query, we will get the min and the max required by dates:

SELECT ProductID,
COUNT(*) as CountOf,
Min(RequiredBy) As Earliest,
Max(RequiredBy) As Latest
FROM yourTable
GROUP BY ProductID

Save it, say, under the name Q1

Next, new query, bring Q1 and Iotas. In a free column, type


SELECT ProductID, CountOf
FROM Q1 INNER JOIN Iotas
ON ( Datepart("ww", Earliest ) + Iotas.Iota
<= DatePart("ww", Latest) )

or, probably a little bit faster:

SELECT ProductID, CountOf
FROM Q1 INNER JOIN Iotas
ON ( Iotas.Iota <= DatePart("ww", Latest) ) - DatePart("ww", Earliest) )




You can create the table Iotas quite easily, if you first create a table, Ds, one field, D, with
values from 0 to 9. Next, make a query and bring Ds twice. Type, in a free column:

Iota: Ds.d + 10*Ds_1.d

and make a table out of it (call it Iotas). Once the table done, edit it to make its field Iota a
primary key.



Hoping it may help,
Vanderghast, Access MVP