Pivot Table Groping 450 points

I have asked this question before but recieved no answer, i will give 450 points if someone will answer this.  The rest of the points were used for the same question 2 previous times. I will accept the correct answer for all 3 questions i have out.
 
All I need is for my pivot table to group results by 7 day weeks, 4 week periods, and 13 period years.  Given that our first period for 2003 started Dec 29 and will end Jan 25, then period 2 starts on Jan 26 and ends Feb 22... and keeps going on like that in a 28 day period format until the end of the thirteenth period.  Then starts back at period 1. The default grouping options for the pivot table allow standard weeks, months, quaters and years.  I just need a way to tell the pivot table to group the results by 7 day weeks and 4 week periods.  Starting from a date that I provide. Not as important but i would also like to be able to add in the 5th week for period 13 that will happen in a few years.  Thanks in advance.
SeanB43It DirectorAsked:
Who is Participating?
 
lluddenConnect With a Mentor Commented:
Assume basic table of:

idx
dDate
Amount


Pass query the starting date of a period: 12/28/03=@SDate

Select
    (SELECT SUM(Amount) FROM Table1 WHERE dDate IS BETWEEN @SDate AND @SDate+6) as Week1,
    (SELECT SUM(Amount) FROM Table1 WHERE dDate IS BETWEEN @SDate+7 AND @SDate+13) as Week2,
    (SELECT SUM(Amount) FROM Table1 WHERE dDate IS BETWEEN @SDate+14 AND @SDate+20) as Week3,
    (SELECT SUM(Amount) FROM Table1 WHERE dDate IS BETWEEN @SDate+21 AND @SDate+27) as Week4,
    (SELECT SUM(Amount) FROM Table1 WHERE dDate IS BETWEEN @SDate AND @SDate+27) as Period1
<etc etc>
FROM Table1

Kind of ugly, but it should work


0
 
SeanB43It DirectorAuthor Commented:
I forgot to give some other info.  I am using access to link to dbf files which are updated every day.  It seams i "can't use a pass query when linking to external foxpro dbase" or so says access.  Maby i am misunderstanding but i dont care if the query groups the results as long as it is grouped in the pivot table.
0
All Courses

From novice to tech pro — start learning today.