Solved

Access bar chart date axis

Posted on 2010-09-23
12
918 Views
Last Modified: 2012-05-10
I have a number of charts in Access 2007 created with MS Graph that I would like to have a horizontal date axis in months.  

Currently the axis displays months because I have set the axis majorunit to 365.25/4 and the minorunit to 30.4375 which produces a result that is close but the first date is 1-Aug-07 and the next date is 31-Oct-07.  I am not really interested in the one day descrepancy but the axis is confusing to read because the day error changes the month so, for example the 31-Oct-07 would look a lot better if it was 1-Nov-07.  

I would like to specify the start date and end date and specify the units as months instead of 30.4375 days so each majorunit starts with the same day of the month.  How do I specify the unit as 'months' rather than days?

Thanks,
merdeka
0
Comment
Question by:merdeka
  • 6
  • 6
12 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Again,

If the data you are plotting are dates, the chart wizard will do this for you.

I don't see the need for any " 365.25/4" math calculations.

Can you give us some info on what you are trying to Graph?

JeffCoachman
0
 

Author Comment

by:merdeka
Comment Utility
I'll send a reply tomorrow.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
If you just want the Full month Name, then change the formatting of the Charts rowsource.

It maybe something like this:
TRANSFORM Sum([YourTable.Amount]) AS [SumOfAmount] SELECT (Format([TheDate],"MMM 'YY")) FROM [YourTable]  
GROUP BY (Year([TheDate])*12 + Month([TheDate])-1),(Format([TheDate],"MMM 'YY"))
PIVOT Yourtable.[EmployeeiD];

Change it to something like this:
TRANSFORM Sum(YourTable.Amount) AS SumOfAmount SELECT (Format([TheDate],"mmmm")) AS FullMonthName
FROM YourTable
GROUP BY (Year([TheDate])*12+Month([TheDate])-1), (Format([TheDate],"mmmm"))
PIVOT YourTable.EmployeeiD;

Screenshot and sample attached

untitled.JPG
Access-EEQ26496439ChartDisplayFu.mdb
0
 

Author Comment

by:merdeka
Comment Utility
Jeff,

Attached is a sample of the chart image, sample data (in an Excel file), and the query that created the data.  The chart might be more accurately described as a schedule because the horizontal bars can start and end anywhere.

This is one of four graphs which I overlay and that's the reason there are three blank rows in the data (the overlays provide similar bars in different colors that fill-in the B and E rows, there is one more row that is composed of the same date data (the current month date - I actually made this 4 days wide so it is more visible) that appears opposite the long description.

The chart extends beyond the bottom of the page so following pages have no header - I will submit another question for that problem later :-)

Thanks,
merdeka
SELECT [tbl_ProcurementCode].[Code_L1] & " -F" AS [Desc], tbl_ProcurementCode.FinishDate, tbl_ProcurementCode.StartDate

FROM tbl_ProcurementCode

WHERE (((tbl_ProcurementCode.w05_projcode)=[Forms]![frmSelectProject]![cmboProject]))

 UNION SELECT [Code_L1] & " -E" AS Category, "" AS start, "" AS finish

FROM tbl_ProcurementCode

WHERE (((tbl_ProcurementCode.w05_projcode)=[Forms]![frmSelectProject]![cmboProject])) UNION SELECT [Code_L1] & "  - " & [Desc_L1] & "  -------------" AS Category, "" AS start, "" AS finish

FROM tbl_ProcurementCode

WHERE (((tbl_ProcurementCode.w05_projcode)=[Forms]![frmSelectProject]![cmboProject])) UNION SELECT [Code_L1] & " -B" AS Category, "" AS start, "" AS finish

FROM tbl_ProcurementCode

WHERE (((tbl_ProcurementCode.w05_projcode)=[Forms]![frmSelectProject]![cmboProject]));

Open in new window

rptFA.pdf
rptFAdata.xls
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
So again, if you format the Charts Rowsource Date field to "MMMM" then you should be good to go.
0
 

Author Comment

by:merdeka
Comment Utility
Jeff,

The Rowsource (for the bars) has to be specific days - unless I am missing the point regarding the Rowsource.  The horizontal axis labels should be in months and years.  

I can format the axis label to months and years but the 01-Aug-07 appears as Aug-07, which is correct, but the 31-Oct-07 appears as Oct-07 when it should (or I want it to) appear as Nov-07.  I could also add three days to the first date to ensure the month is almost always correct but then the bars will be out a bit relative to the axis.

merdeka
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
I'm confused.

First you said:
< I would like to have a horizontal date axis in months.>
"Months", meaning you wanted the Month name only...

Now you say:
<The horizontal axis labels should be in months and years>
Now you want Month and Year?

Can you please state what you are wanting to display "Exactly"...?

I think we are at the point where a sample DB is needed.

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Delete any objects that do not relate directly to the issue.
5. Remove anr references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
6. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
7. Compile the code. (From the database window, click: Debug-->Compile)
8. Run the compact/Repair utility.
9. Remove any Passwords and/or security.
10. Post explicit steps to replicate the issue.
11. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see the issue.


Thanks

JeffCoachman

0
 

Author Comment

by:merdeka
Comment Utility
Jeff,

Sorry, you are right, the problem is the day is wrong causing the month to be wrong and eventually causing the year to be wrong.  I was concentrating on the month because eventually, if I could get the date right,  I was going to hide the day and just display the month and year.  

Did you look at the pdf file I attached?  The dates are along the top and the bars are horizontal - the bars are not vertical and there is no aggregating of data.  

Attached is the stripped-down database.  Open frmSelectProject and select the only project 'North Mine'.  Click on the button with the report icon to display the report.

The dates along the top are not in even month increments, they vary by a few days and make the dates awkward to read quickly.

I hope this is a bit more precise.

Thanks,
merdeka
ProcurementPlan.accdb
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
You have a few issues here:
1. Why are you breaking the dates unto pieces in the code?
2. If you are showing one data range for B, E, and F then why do you need to show them individually?
Why not do something like this:
C001  - Civil/Earthworks Const. services  -------------    10/1/2008    6/13/2008
C002  - Site Svc - Temp. Non-process Power&pipng  -------------    6/30/2008    5/7/2008
...      

3. 365.25 is not really accurate, the actual value is closer to 365.2425 days for an average Gregorian year.
Besides this is never accurate for any "1" year, it is only used as an Average of "All years".
See here for more on this discussion:
http://en.wikipedia.org/wiki/Year

Finally if this "Gantt Chart" is what your ultimate goal os then remember that a lot of them are based on the number of days, not really the  Date.

In any event, ...Investigate these links perhaps:
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=13762&d=1149173937
http://www.bandwood.com/gbs.htm
(see the links to samples)

JeffCoachman

0
 

Author Comment

by:merdeka
Comment Utility
Jeff,

The other bars are on other graphs which are overlayed on the graph I uploaded.  Each graph is set to transparent so the overlays show the other bars - the black bar only appears in the first few records because I haven't entered more test data.

I looked at the Wikipedia Year discussion but because we use the Gregorian calendar which has either 365 days or one additional day evey four years I think 365.25 is correct (they correct for the 0.0075 day error periodically but it's once in 133 years approximately (my calc of 1/.0075) to bring us back in line with the sun).  I suspect that's the reason the year 2000 wasn't a leap year, maybe this was mentioned in the Wikipedia article but I didn't read it all.  Anyway the real problem is caused by the fact that even though the 'quarters' are called quarters they do not occur exactly at quarters of years.

The Gantt Chart is what I'm trying to produce and I may buy the one from the web site you suggested for other purposs and eventually for the probelm at hand.  

After thinking about the axis problem a bit more I realize there is no built in Access graph solution so what I plan to do is add eight TextBoxes (one for each quarter) and place them where the current axis is.  I will then populate the TextBoxes from tbl_MonthAxisTmp which I have changed so it contains dates for quarters instead of months.  The dates from the Access axis will be hidden and the dates from the table displayed.

Even though the dates displayed will be slightly dfferent than the vertical quarter bars on the graph the two will be close enough.  In other words the one or two day difference won't be noticeable but having quarter dates rather than approximate quarter dates will make it easier for the reader.

For your information I have attached an unfinished graph (it doesn't have the new quarters yet) but it shows four graphs overlaid and bar opposite the B,E, F, and blue dot opposite title lines.  It's not very pretty but I think it will suffice for now.

I accept your Gantt Chart as the solution because I think it will work and it is probably a better solution than mine.

Thanks very much for you help.  This has been an interesting problem for me from the start and I hope it has been for you too.

merdeka
rptCombined.pdf
0
 

Author Closing Comment

by:merdeka
Comment Utility
Thanks very much for your help.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
;-)
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now