Solved

Access MS Graph date axis

Posted on 2010-09-16
24
726 Views
Last Modified: 2012-05-10
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
Comment
Question by:merdeka
  • 14
  • 10
24 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 33699775
Attach a database with the data, and an image of the expected result.
0
 

Author Comment

by:merdeka
ID: 33709469
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
 
LVL 30

Expert Comment

by:hnasr
ID: 33710756
Sorry unable to understand the problem!
0
 

Author Comment

by:merdeka
ID: 33711855
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
 
LVL 30

Expert Comment

by:hnasr
ID: 33711889
"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
 

Author Comment

by:merdeka
ID: 33712068
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
 

Author Comment

by:merdeka
ID: 33712089
hnasr,

Attached is an Access database with one table.

merdeka
PPlanTemp.accdb
0
 
LVL 30

Expert Comment

by:hnasr
ID: 33712273
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
 

Author Comment

by:merdeka
ID: 33712380
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
 

Author Comment

by:merdeka
ID: 33712443
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
 
LVL 30

Expert Comment

by:hnasr
ID: 33712728
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
 
LVL 30

Expert Comment

by:hnasr
ID: 33712730
I mean, I can't see the logic of plotting the dates, as bar chart type.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:merdeka
ID: 33712852
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
 

Author Comment

by:merdeka
ID: 33712858
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
 
LVL 30

Expert Comment

by:hnasr
ID: 33712984
Then?
Query2 shows the date axis by sunning start + finish

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

Author Comment

by:merdeka
ID: 33713518
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
 
LVL 30

Expert Comment

by:hnasr
ID: 33713668
Sorry! Here is the database.
PPlanTemp-3.accdb
0
 

Author Comment

by:merdeka
ID: 33718825
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
 

Author Comment

by:merdeka
ID: 33721795
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
 
LVL 30

Accepted Solution

by:
hnasr earned 500 total points
ID: 33721887
" 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
 

Author Comment

by:merdeka
ID: 33722096
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
 

Author Comment

by:merdeka
ID: 33722128
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
 

Author Closing Comment

by:merdeka
ID: 33722133
It was a complicated unusual graph.

Thanks for the help,
merdeka
0
 
LVL 30

Expert Comment

by:hnasr
ID: 33722559
Welcome!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

705 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

19 Experts available now in Live!

Get 1:1 Help Now