By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,487 Members | 1,735 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,487 IT Pros & Developers. It's quick & easy.

Access Chart

P: 21
I am looking to chart some suspiciously simple data.

Project StartDate EndDate StartPoint EndPoint
No 1 7/4/2007 7/8/2007 5 12
No 2 9/12/2007 9/14/2007 9 14

etc..

I would like to use the start dates and end dates as Y axis points and start and end points as X axis points and label each resulting rectangle with the Project Number. The resulting chart would overlap rectangles from 9 to 12 on the x axis. I have seen the microsoft post on removing the summary but none of the charting objects seem to allow a range to represent each record on each of the two typical axis. Further: I have yet to find an object that will allow this definition at all.

Thank You for any links or tips.
Jan 24 '08 #1
Share this Question
Share on Google+
11 Replies


Expert 100+
P: 446
Hi
When I wanted a 'simple' chart I ended up drawing rectangles, setting Height in cms to pixels by multiplying by 567.

I only wanted to show 12 values, so I had Box0 thro Box11 'pre-drawn' and just changed their height in a loop. It would be possible to change their 'Left' and 'width' with code.

It was a bit tedious working out the scaling and a few other things but it gave me exactly what I wanted.

If you are interested in this apprtoach I can give more details but I admit that towards the end I felt I had re-invented the wheel!

S7
Jan 28 '08 #2

P: 21
I have no other leads at this point so if you have a moment, I could use the details you spoke of.

Thanks

Hi
When I wanted a 'simple' chart I ended up drawing rectangles, setting Height in cms to pixels by multiplying by 567.

I only wanted to show 12 values, so I had Box0 thro Box11 'pre-drawn' and just changed their height in a loop. It would be possible to change their 'Left' and 'width' with code.

It was a bit tedious working out the scaling and a few other things but it gave me exactly what I wanted.

If you are interested in this apprtoach I can give more details but I admit that towards the end I felt I had re-invented the wheel!

S7
Jan 29 '08 #3

Expert 100+
P: 446
I have no other leads at this point so if you have a moment, I could use the details you spoke of.

Thanks
Before we start off down a wrong road, how many 'projects' are you hoping to include in the chart? Becaues of the repetative nature of the code, a nice number would be about 10, although code can probably be improved with loops if you put the time in. But too many overlaid rectangles could be confusing as well.

My chart was for a screen display although I imagine the code could be copied into a Report.

Can you supply a bit more data? or shall I try something with what you have posted . One problem is to determine the 'scale' so at some point you must determine the 'range' of the data values and two sets may not be a fair test.

I'm try to beat the Tax Deadline right now but I'll see if I can find time tomorrow.

S7
Jan 29 '08 #4

P: 21
Short answer: 10 sounds good.

Since the data is coming from a user input on a filter page I was going to limit the range based on their selections. Having said that, the number of rectangles will flux but not beyond what is legible. Once I begin layout I will be able to better determine those limits.


Before we start off down a wrong road, how many 'projects' are you hoping to include in the chart? Becaues of the repetative nature of the code, a nice number would be about 10, although code can probably be improved with loops if you put the time in. But too many overlaid rectangles could be confusing as well.

My chart was for a screen display although I imagine the code could be copied into a Report.

Can you supply a bit more data? or shall I try something with what you have posted . One problem is to determine the 'scale' so at some point you must determine the 'range' of the data values and two sets may not be a fair test.

I'm try to beat the Tax Deadline right now but I'll see if I can find time tomorrow.

S7
Jan 29 '08 #5

Expert 100+
P: 446
Short answer: 10 sounds good.

Since the data is coming from a user input on a filter page I was going to limit the range based on their selections. Having said that, the number of rectangles will flux but not beyond what is legible. Once I begin layout I will be able to better determine those limits.
I'm attaching db3 which should open and show a chart along the lines of what I understand you would like.

The Y-axis is set to '2007' and X-axis set to 0-25 Startpoints, whatever they are. There is no error handling or data verification

Some of your projects only have 2 days between start and finish date so they appear as very slim boxes at this scaling but the purpose of this is a testbed for ideas. You can add additional data rows manually through the subform on the chart. The exact locations of the 'boxes' need adjusting by a line width or so but this is not a final solution.

By making the 'boxes' on the chart TextBoxes they can receive focus so you could click on them to initiate other actions. If the box is too slim to read the text inside the box then you can use the Shift+F2 Zoom function. There is another form purely to manually add "Project Titles"

The chart is based on a local table from which you would have to delete data prior to re-populating with your real data. You would then have to run a loop to number the rows 1 thro 10, unless you handled this in append query.

Please let me know if this has been of any use.

S7
Jan 30 '08 #6

jaxjagfan
Expert 100+
P: 254
Here's a sample of how I referenced a Chart with code. I have a form with 2 charts on it (Graph1 and Graph2)

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3. With Me.Graph1.Axes(xlValue)
  4. Select Case Me.cboGrp
  5. Case "BCI"
  6. .MaximumScale = 2500
  7. .MinimumScale = -1000
  8. .MajorUnit = 500
  9. Case "BCL"
  10. .MaximumScale = 300
  11. .MinimumScale = -300
  12. .MajorUnit = 100
  13. End Select
  14. End With
  15.  
  16. With Me.Graph2.Axes(xlValue)
  17. Select Case Me.cboGrp
  18. Case "BCI"
  19. .MaximumScale = 2500
  20. .MinimumScale = -1000
  21. .MajorUnit = 500
  22. Case "BCL"
  23. .MaximumScale = 300
  24. .MinimumScale = -300
  25. .MajorUnit = 100
  26. End Select
  27. End With
  28.  
  29. End Sub
  30.  
Jan 30 '08 #7

Expert 100+
P: 446
My database db3 did not seem to attach properly so I am posting it again
Attached Files
File Type: zip db3.zip (35.9 KB, 222 views)
Jan 30 '08 #8

P: 21
Reinvention of the rectangle. Thank you for the post! I'll be looking at this a little later today. If I alter/use your solution, I will repost.
My database db3 did not seem to attach properly so I am posting it again
Jan 30 '08 #9

Expert 100+
P: 446
Here's a sample of how I referenced a Chart with code. I have a form with 2 charts on it (Graph1 and Graph2)
Hi Jax
It's interesting that you can reference a Graph like that. I usually just leave the scaling to the graph object to do automatically, although sometimes wish I had more control.

Mr Sconard's problem seemed to be that he could not find a suitable type of graph within MSGraph, hence the "Revenge of the Rectangles!"

I only started doing this because I am developing a CRM interface for one of my clients and wanted to include a minature display of the last 12 mths Sales history in a 1.5" x 2.0" window, and could not be bothered to struggle with MSGraph. It may not have been the right decision . . .

S7
Feb 1 '08 #10

jaxjagfan
Expert 100+
P: 254
Hi Jax
It's interesting that you can reference a Graph like that. I usually just leave the scaling to the graph object to do automatically, although sometimes wish I had more control.

Mr Sconard's problem seemed to be that he could not find a suitable type of graph within MSGraph, hence the "Revenge of the Rectangles!"

I only started doing this because I am developing a CRM interface for one of my clients and wanted to include a minature display of the last 12 mths Sales history in a 1.5" x 2.0" window, and could not be bothered to struggle with MSGraph. It may not have been the right decision . . .

S7
That size is slightly larger than "thumbnail" and would not of much value other than "somerthing pretty" on the form or report.

You can reference and dynamically control the graph object and its pieces. One group within the company done far less business than the other so I had to dynamically set the scale so the values would properly register on the chart.

I learned how to do that piece 6 yrs ago - just thought it would give ya some ideas.

Hope it helps.
Feb 1 '08 #11

Expert 100+
P: 446
That size is slightly larger than "thumbnail" and would not of much value other than "somerthing pretty" on the form or report.
It's a bit more than eye-candy Jax! The alternative would be a single number to show last years turn-over (they have that too). "A picture says a thousand words"

This interface is for Salemen negotiating prices on the telephone. There is a stack more info on the screen. The 'chart' shows a profile of when previous sales occured but more importantly, "Have they been paid ?" We interface with Sage and spread the customer's Current Balance over their Sales profile, so unpaid sales show-up red. A company can be below its Credit Limit but be owing you money for months.

Another advantage of drawing the chart 'manually' (rather than using MSGraph) is that the columns in the display are TextBoxes, so can recieve focus, so can be 'clicked' to open up the actual Sales Orders for that month, or whatever.

Anyway, this not withstanding, I do use MSGraph in reports and it's useful to know that it can be controlled programmatically. Thanks

S7
Feb 1 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.