By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,948 Members | 795 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,948 IT Pros & Developers. It's quick & easy.

showing every quarter between 2 dates

P: n/a
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

Mar 15 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.

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

Mar 15 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.