Access bar chart date axis

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

I'll send a reply tomorrow.

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

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]));

Select allOpen in new window

rptFA.pdf
rptFAdata.xls

So again, if you format the Charts Rowsource Date field to "MMMM" then you should be good to go.

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

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

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

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

Thanks very much for your help.

;-)