Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access Pivot Chart: how to show week increments across top of bar chart

Posted on 2011-03-03
15
Medium Priority
?
627 Views
Last Modified: 2012-05-11
I'm trying to create a pivot chart that is similar to a gantt chart in some ways. The Bar Chart format is what I'm using. In the 'column' on the left, going down in a list, are Task descriptions. Each task is a record that has a beginning and ending date. I need on the horizontal top of the chart to show weekly markers, the date changing depending on the current date (meaning each marker is a week out, in ongoing sequence from todays date). How do you set that up?

That's just for the header of the chart. Then I need the 'bar' for each task (record) to reflect its beginning date and ending date. Meaning the bar starts under the column of the header date the begin date corresponds with. And the bar ends under the column of the header date the Ending Date corresponds with.

I don't have a lot of experience with Pivot Charts so any advice would be greatly appreciated.
0
Comment
Question by:AccessWrangler
[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
  • 8
  • 7
15 Comments
 
LVL 40

Expert Comment

by:als315
ID: 35033934
You can download an example of Gantt diagram here:
http://www.access-programmers.co.uk/forums/showthread.php?t=108369
0
 

Author Comment

by:AccessWrangler
ID: 35037543
Thanks for the link- I've looked at that before and it doesn't quite do what I need. I need to do this on a form in Pivot View. I have figured out how to get the 'bars' to look right and measure the correct values but so far I cannot get the grid lines to show date values (each vertical line a date, 1 week apart). If you have any idea how to do that I'd appreciate it.
0
 
LVL 40

Expert Comment

by:als315
ID: 35037662
Can you upload DB with sample data?
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

by:AccessWrangler
ID: 35038152
I've attached a sample db of the issue. Hopefully it'll clarify what I need to do. Thanks for taking a look.
PivotChartExample.zip
0
 
LVL 40

Expert Comment

by:als315
ID: 35041836
I have only one idea (not 100% sure):
now your data are following:
Task, Beginning Date, Duration
Try to change it to values for every date in task range:
Task, Beginning Date, 1
Task, Beginning Date+1, 1
....
Task, Beginning Date+Duration -1, 1
May be it can help.
0
 

Author Comment

by:AccessWrangler
ID: 35043395
Thanks for the help. Are you meaning to put this as an expression in the underlying query? I'm not sure of what the syntax is and exactly where it goes. I'll see if I can work it in but if you could clarify exactly where this should go and the syntax it would be really helpful. Many thanks.
0
 
LVL 40

Expert Comment

by:als315
ID: 35043442
I will try to made example with your test DB, but this idea is not clear enough.
0
 

Author Comment

by:AccessWrangler
ID: 35043454
Thanks- I really appreciate that. I'm not quite getting the concept and that would help a great deal.
0
 
LVL 40

Expert Comment

by:als315
ID: 35045963
My first idea was wrong.
Test included example (form qryTimelinePivot). May be it can be a step in right direction.
I'm not sure it is possible to set today as zero axis.
PivotChartExample.zip
0
 

Author Comment

by:AccessWrangler
ID: 35049835
I think that's a great step in the right direction. Thanks very, very much for putting the time into that. One question, though- how does is the first date on the horizontal axis calculated? I just need to make sure that, as time goes on, the chart resets itself with relevant dates and doesn't keep starting from a very old date. But this looks like it's really in the right direction. thanks again.
0
 

Author Comment

by:AccessWrangler
ID: 35049915
One thing also- it seems the End Dates are not accurate on the bars. the BeginDates are- would you have any idea why that changed on it? I'd need them to fall on the right date.
0
 
LVL 40

Expert Comment

by:als315
ID: 35053451
You should leave DaysTillDue as Date
DaysTillDue: [DueDate]-[BeginDate]
0
 
LVL 40

Accepted Solution

by:
als315 earned 2000 total points
ID: 35053609
This is example with your idea of different bar colors till Now and after
PivotChartExample.zip
0
 

Author Comment

by:AccessWrangler
ID: 35058978
Ah, that works better- I think I understand how you tweaked it now. I really appreciate your time on that. Many thanks!
0
 

Author Closing Comment

by:AccessWrangler
ID: 35058988
Was very helpful in coming up with a working solution. I'm not completely understanding how it was done but should be able to go in and figure it out.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

721 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