|
Subject:
|
Charts in Access
|
|
Posted By:
|
lbreitenbach
|
Post Date:
|
12/8/2006 2:59:35 PM
|
Good afternoon!
Is there a way to create a chart on a form in Access and chart more than 6 items?
I have data to create a chart for grouped by week, but there are about 14 other fields I need it to graph as the data for the bars.
Any help or articles on how to graph more items would be much appreciated!
Have a wonderful weekend!
Regards,
Laura
The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
12/11/2006 7:51:45 AM
|
As it happens, I had an article in Access Advisor for this very subject.
You can do this, but only with a bar chart. If you are interested, I will post the text of that article. If you get Access Advisor, it was tip of the month for January 2006.
mmcdonal
|
|
Reply By:
|
lbreitenbach
|
Reply Date:
|
12/11/2006 7:58:40 AM
|
Good morning!
Yes, please share this information with me. I do not get Access Advisor, but it sounds like I should look into it. If it is not in an electronic form, I hate to ask you to type it up. If scanning it in would be easier on you, you can send it as an attachment to me directly at LRYCKMAN@STANTINC.COM
Thanks!
Regards,
Laura
The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
12/11/2006 8:14:50 AM
|
There is a similar chapter in Access Cookbook by O'Reilly (sorry Wrox). Here is that one.
Create a report, including text data you'd like to show for each row (must have numeric data for each row). (assume controls txtName and txtScore)
Add a rectagle control and place it next to the data in the detail section (should look like)
Detail Section:
txtName txtScore [rctBar]
You can also use a label. Set the background to a pretty color.
Set the height of the rectangle or label to match the text boxes.
I also add a line smack under each label or rectangle to seperate rows. Hold down Shift as you draw the line to make sure it is perfectly horizontal.
Be sure to look at the design view of the report and make sure the display area for the bar is a set number of inches. 4" is good for portrait mode, and 6" is good for landscape.
To set the width of the rectangle or label for each row, use this code on the On Format event of the detail section:
For 4" width:
Me.rctBar.Width = (Me.txtScore / 100) * (1440 * 4)
For 6" width:
Me.rctBar.Width = (Me.txtScore / 100) * (1440 * 4)
For my article, I added the following:
If you want all the rows in the report related to one another such that they display their percentage of 100% when compared to all the other records, then you can add this:
Place a text box in the report header, and sum the txtScore values, like this. Call it txtSum:
=Sum([txtScore])
Add an unbound text box to the right hand side of the report outside of the bar display area. Remove its label. Call it txtPerCent
Then add this code to the Detail section's On Format event:
Dim iSum, iScore As Integer
iSum = Me.txtSum
iScore = Me.txtScore
Me.rctBar.Width = (iScore / iSum) * (1440 * 4)
Me.txtPerCent = (iScore / iSum)
Format txtPerCent to display percent.
This will add a bar chart to the report and the bars will all be related to one another such that the total of their lengths = 100%
If you are very clever, you can also create a report by setting bar heights, but you need to get all your values in one record / row. I have done this as well.
This method will display n number of records in the bar chart, but as a practical matter, you are limited to about 20 records.
HTH
mmcdonal
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
12/11/2006 8:16:13 AM
|
P.S. for the 4" width, put a label in the header at 1" and put "25%", a label at 2" with "50%", a label at 3" with "75%" and one at 4" with "100%".
mmcdonal
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
12/11/2006 8:17:20 AM
|
Sorry: Correction:
For 6" width:
Me.rctBar.Width = (Me.txtScore / 100) * (1440 * 6)
mmcdonal
|
