On Mar 14, 8:42 pm, mnol...@jeffco. us 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.
qryQuartersByCl ient:
SELECT DISTINCT Year(DateAdd("q ", tblIntegers.the Int - 1,
tblData.sbegin) ) As theYear, DatePart("q", DateAdd("q",
tblIntegers.the Int - 1, tblData.sbegin) ) As theQuarter, Client FROM
tblData, tblIntegers WHERE theInt <= 9 AND DateAdd("q",
tblIntegers.the Int - 1, tblData.sbegin) BETWEEN tblData.sbegin AND
tblData.send;
!qryQuartersByC lient:
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********@Fort uneJames.com 
