?
Solved

Pivot Table Groping 450 points

Posted on 2003-03-03
2
Medium Priority
?
365 Views
Last Modified: 2006-11-17
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.
0
Comment
Question by:SeanB43
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 18

Accepted Solution

by:
lludden earned 520 total points
ID: 8059796
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
 

Author Comment

by:SeanB43
ID: 8060058
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

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question