• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 778
  • Last Modified:

Access MS Graph date axis

I'm using Access 2007 to create a graph with text values on the vertical (x) axis
 and dates on the horizontal (z) axis. I don't know what happened to the y axis, it's disabled.

I can't get the z axis to display dates (year and month or any date) because the Date Value option doesn't display as an option .

The data table is in the format:

Description          datevalueA        datevalueB

I want the graph to display a bar starting at datevalueA extending to datevalueB parallel to the z axis (i.e. horizontal bars) and display the Description label on the x axis.

Thanks,
merdeka
0
merdeka
Asked:
merdeka
  • 14
  • 10
1 Solution
 
hnasrCommented:
Attach a database with the data, and an image of the expected result.
0
 
merdekaAuthor Commented:
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
0
 
hnasrCommented:
Sorry unable to understand the problem!
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
merdekaAuthor Commented:
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
0
 
hnasrCommented:
"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.
0
 
merdekaAuthor Commented:
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
0
 
merdekaAuthor Commented:
hnasr,

Attached is an Access database with one table.

merdeka
PPlanTemp.accdb
0
 
hnasrCommented:
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.
0
 
merdekaAuthor Commented:
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
0
 
merdekaAuthor Commented:
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
0
 
hnasrCommented:
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
0
 
hnasrCommented:
I mean, I can't see the logic of plotting the dates, as bar chart type.
0
 
merdekaAuthor Commented:
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  
0
 
merdekaAuthor Commented:
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
0
 
hnasrCommented:
Then?
Query2 shows the date axis by sunning start + finish

How will interpret a value like 9/21/2120? (see query2)
0
 
merdekaAuthor Commented:
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.
0
 
hnasrCommented:
Sorry! Here is the database.
PPlanTemp-3.accdb
0
 
merdekaAuthor Commented:
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

0
 
merdekaAuthor Commented:
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
0
 
hnasrCommented:
" paste one graph over the other nothing happens"

Select graph from control name box in properties window
right click:copy
right click:paste
watch new control name in properties window.
0
 
merdekaAuthor Commented:
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
0
 
merdekaAuthor Commented:
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
0
 
merdekaAuthor Commented:
It was a complicated unusual graph.

Thanks for the help,
merdeka
0
 
hnasrCommented:
Welcome!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 14
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now