Access MS Graph date axis

Attach a database with the data, and an image of the expected result.

hnasr,

Attached is an Excel file with data and the expected result and a few notes.  I don't have all of the data for the graph yet because we had a meeting yesterday and the graph was changed to the format in the attached excel file.  The location of the dates doesn't matter - the bottom of the graph is fine.

Thanks,
merdeka  
EEProcPlanGraphSample.xls

Sorry unable to understand the problem!

hnasr,

The problem is that when I try to plot the graph I can't get the horizontal scale (z axis I believe it's called) to represent the dates and the vertical scale (x axis) to represent the categories.

I want the bars of the graph to be horizontal and date scale to appear at the bottom of the graph along the horizontal axis.

Maybe the answer is too obvious.

merdeka

"Maybe the answer is too obvious."
I am sure it is obvious to you because information is in your head.

Since we talk access, I expect a simple database that shows the issue. Add  pictures of the output, and the expected result.

hnasr,

Sorry, what I meant is that the question is hard to understand because to you the answer is too obvious.  In other words I am too dumb to to see the answer - I didn't mean anything about you.  

I certainly didn't mean any insult to you.  Sometimes my jokes don't sound too funny.

I did include an excel file with the expected graph and the table I am using but I will put the Excel data in an Access database file.  Is the Excel file OK as a picture of the expected result?

merdeka

hnasr,

Attached is an Access database with one table.

merdeka
PPlanTemp.accdb

merdeka,

No worries.
Please attach an image of the expected output drawn in paint or scanned from paper.

You may reduce the number of records to help in drawing the output manually.

hnasr,

Attached is a graph I created in Excel (bmp image) based on the data I uploaded about an hour ago.

It is a stacked bar graph.  The graph has two horizontal columns but I set the border to 'no color' and the fill to 'no fill' for the bars on the left (which, as a result, are now invisible) but it shows the bars starting to the right of the vertical axis which is what I want.

The dates on the horizontal axis start at 1/0/1900 and end at 5/29/2146 - I don't know wher these dates came from.  I thought they might be caused by the records with no date so I deleted those records but the horizontal axis didn't change.

Anyway the attached image is the concept I want but the date data is wrong.

merdeka
Graph.bmp

hnasr,

Here's another image that is a lot closer.  It seems the dates were added together in the previous image so I changed to a cluster bar chart which doesn't add the values.  Then I overlapped the bars with the top bars (left bars) colored white.

As you can see from the data there are three of each item on the vertical axis (some don't in the example because I deleted items with no date but I will change that in the real data).  I want these to be colored in three different colors but have the colors repeat for each group of three.

merdeka
Graph.bmp

merdeka,

I can't see the logic of plotting the dates.
Check form1, and form2 represnting a sample of the data and comment on them.
PPlanTemp-2.accdb

I mean, I can't see the logic of plotting the dates, as bar chart type.

hnasr,

The purpose of the chart is to show start and finish dates of activities similar to a MS Project schedue.  The three bars represent: baseline, actual, and progress.  

Each of the three items (on the vertical axis with the same description) describes a different aspect of an activity - I didn't include the three types in the database I uploaded.  For example Wildlife Study has three bars, one bar is for the baseline or planned start and finish dates of the activity, another the actual start and actual finish date (or forecast finish for future dates) of the activity, and one is or the progress to date for the activity.

merdeka  

hnasr,

It looks like I did include the three categories.

I looked at the graphs in the Access database you sent, Form2 is the closest and is the right concept.

merdeka

Then?
Query2 shows the date axis by sunning start + finish

How will interpret a value like 9/21/2120? (see query2)

hnasr,

I don't see Query2 unless you are referring the the query for Form2 but then I don't see the date 9/21/2120 on the graph or in the tables.

merdeka.

Sorry! Here is the database.
PPlanTemp-3.accdb

hnasr,

Query2 adds the dates and that's why the values are so far in the future.  

In Form2 the dates are also added but if a clustered bar chart is used they are not and this shows the correct finish dates but unfortunaely they all start at the origin or vertical axis.

The problem is that I need bars that don't start at the vertical axis and I don't think this is a standard Access chart.  I will try having the time from the origin to the start date be a bar that is white color and overlaps the first part of the longer bar producing the effect of a bar that doesn't start at the vertical axis.  This works but I am not sure how to produce three bars for each category each one with a white overlapping bar and have each bar be a different color.

Thanks,
merdeka

hnasr,

I have created three graphs and ech one has one of the date pairs.  The graphs have exactly the same layout except that the date pairs (bars and data) are on different rows.  I have set the backgrounds to transparent with the idea that each graph can be pasted over the other and the lower level data will be visible because the graph backgrounds are transparent.

The problem now is that when I try to paste one graph over the other nothing happens.  In other words there is no graph pasted on top of the first graph, there is just the first graph.  I created a new blank report and tried pasting one report over the other but no luck.

I know this is a wierd graph problem but the Access graph software seems to be very unintuitive and the fact that I have no experience with it doesn't help.  It looks like transparent may mean the the color of the underlying form is visible through the graph but other objects can't be underneath the graph.

Thanks,
merdeka

hnasr,

OK, it looks like all three graphs were actually pasted but only one, the original graph, is visible.  

When I select one of the other graphs from the property sheet the border doesn't highlight in orange as it does when I choose he original graph.  I thought this was because the original graph border was on top of the other graph borders but when I try to move the top graph there is nothing exposed below it so I don't know where the other two graph objects are.  

merdeka

hnasr,

I think I have it.  I have one graph over the other and, although they are not exactly aligned yet, I think it's going to wok.

Thanks,
merdeka

It was a complicated unusual graph.

Thanks for the help,
merdeka

Welcome!