Solved

Access MS Graph date axis

Posted on 2010-09-16
24
737 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help writing a query 6 73
Is it possible to reset DSum? 12 43
MS Access Order Smallest to Biggest Query Help 13 43
Getting Run-Time Error 13 - Type Mismatch 3 28
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

867 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

16 Experts available now in Live!

Get 1:1 Help Now