showing every quarter between 2 dates

[mnoland]

I relay need some help I am looking to find every quarter between 2
dates here is an example of what i am trying to do

data table

client sbegin send
499 1-1-2000 6-1-2001
499 12-12-05 12-21-05
499 10-5-06 12-15-06
499 12-1-06 12-10-06
499 6-5-07 6-6-07
so the results would be

client Quarter Year
499 1 00
499 2 00
499 3 00
499 4 00
499 1 01
499 2 01
499 4 05
499 4 06
499 2 07

How would i do this

[CDMAPoster]

On Mar 14, 8:42 pm, [e-mail] wrote:

I relay need some help I am looking to find every quarter between 2
dates here is an example of what i am trying to do

data table

client sbegin send
499 1-1-2000 6-1-2001
499 12-12-05 12-21-05
499 10-5-06 12-15-06
499 12-1-06 12-10-06
499 6-5-07 6-6-07

so the results would be

client Quarter Year
499 1 00
499 2 00
499 3 00
499 4 00
499 1 01
499 2 01
499 4 05
499 4 06
499 2 07

How would i do this

Auxiliary table method:

tblData
DID AutoNumber
Client Long
sbegin Date/Time m-d-yy
send Date/Time m-d-yy

DID Client sbegin send
1 499 1-1-00 6-1-01
2 499 12-12-05 12-21-05
3 499 10-5-06 12-15-06
4 499 12-1-06 12-10-06
5 499 6-5-07 6-6-07

tblIntegers
ID AutoNumber
theInt Long

ID theInt
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
....

tblIntegers should have enough values to cover the largest number of
quarters expected.

qryQuartersByClient:
SELECT DISTINCT Year(DateAdd("q", tblIntegers.theInt - 1,
tblData.sbegin)) As theYear, DatePart("q", DateAdd("q",
tblIntegers.theInt - 1, tblData.sbegin)) As theQuarter, Client FROM
tblData, tblIntegers WHERE theInt <= 9 AND DateAdd("q",
tblIntegers.theInt - 1, tblData.sbegin) BETWEEN tblData.sbegin AND
tblData.send;

!qryQuartersByClient:
theYear theQuarter Client
2000 1 499
2000 2 499
2000 3 499
2000 4 499
2001 1 499
2001 2 499
2005 4 499
2006 4 499
2007 2 499

Note: This query is not very efficient. I put 'theInt <=9 AND' in for
efficiency in case the auxiliary table is huge. It should be O.K.
without that part. Post back if you need something better and I'll
see what I can do.

James A. Fortune
CD********@FortuneJames.com