Bar Chart based on report calculations...

I am using Access 2002.  I have a query with one field called 
txtUsedKnowledge that has four possible responses.  They are Never, Small 
Extent, Moderate Extent and Great Extent.

I did many calculations with these including counting them individually and 
getting the percent for each one based on the over all count.  Now I would 
like to create a bar chart with the four responses listed that will update 
each time it's run.

I created the chart but have to type in the figures for each response 
myself.  Since the calculations are done on the report and not in the query 
is there a way to use the fields from the report.  Unfortunately, this is my 
first time trying to use the Chart feature in Access.  If I can't do this 
with the Chart option is there another way to have a bar appear on a report?  
Thank you in advance for any help that will be offered.
0
Utf
1/27/2010 4:05:01 PM
access.reports 4434 articles. 0 followers. Follow

4 Replies
1040 Views

Similar Articles

[PageSpeed] 15

Since there are only four elements, I would not create a bar chart. I would 
use a textbox for each element and adjust the width of each textbox to 
represent the percent of each. It would look like:
                                    ____________
Never                         [____________]    35%
                                    ______
Small Extent              [______]                  15%
                                    __________
Moderate Extent       [__________]         30%
                                   ________
Greate Extent           [________]              20%

You can do all the calculations (count, percent, width) in a query. Note 
that percent would be shown in a separate textbox and its Left property 
would also need to be set by the calculated percent. Since all calculations 
are done in a query, the display would aotomatically update as the data 
changed.

Steve
santus@penn.com



"Ann" <Ann@discussions.microsoft.com> wrote in message 
news:6B8F2B94-9B67-47CF-AD14-FF19FFC0428B@microsoft.com...
>I am using Access 2002.  I have a query with one field called
> txtUsedKnowledge that has four possible responses.  They are Never, Small
> Extent, Moderate Extent and Great Extent.
>
> I did many calculations with these including counting them individually 
> and
> getting the percent for each one based on the over all count.  Now I would
> like to create a bar chart with the four responses listed that will update
> each time it's run.
>
> I created the chart but have to type in the figures for each response
> myself.  Since the calculations are done on the report and not in the 
> query
> is there a way to use the fields from the report.  Unfortunately, this is 
> my
> first time trying to use the Chart feature in Access.  If I can't do this
> with the Chart option is there another way to have a bar appear on a 
> report?
> Thank you in advance for any help that will be offered. 


0
Steve
1/27/2010 7:16:27 PM
I'm really rather lost by what you are explaining.  I don't understand how to 
get a textbox to adjust it's size based on a number.  Plus, my calculations 
were not done in the query but on the report in unbound textboxes that are in 
the report footer.  Can you give me more detail?

"Steve" wrote:

> Since there are only four elements, I would not create a bar chart. I would 
> use a textbox for each element and adjust the width of each textbox to 
> represent the percent of each. It would look like:
>                                     ____________
> Never                         [____________]    35%
>                                     ______
> Small Extent              [______]                  15%
>                                     __________
> Moderate Extent       [__________]         30%
>                                    ________
> Greate Extent           [________]              20%
> 
> You can do all the calculations (count, percent, width) in a query. Note 
> that percent would be shown in a separate textbox and its Left property 
> would also need to be set by the calculated percent. Since all calculations 
> are done in a query, the display would aotomatically update as the data 
> changed.
> 
> Steve
> santus@penn.com
> 
> 
> 
> "Ann" <Ann@discussions.microsoft.com> wrote in message 
> news:6B8F2B94-9B67-47CF-AD14-FF19FFC0428B@microsoft.com...
> >I am using Access 2002.  I have a query with one field called
> > txtUsedKnowledge that has four possible responses.  They are Never, Small
> > Extent, Moderate Extent and Great Extent.
> >
> > I did many calculations with these including counting them individually 
> > and
> > getting the percent for each one based on the over all count.  Now I would
> > like to create a bar chart with the four responses listed that will update
> > each time it's run.
> >
> > I created the chart but have to type in the figures for each response
> > myself.  Since the calculations are done on the report and not in the 
> > query
> > is there a way to use the fields from the report.  Unfortunately, this is 
> > my
> > first time trying to use the Chart feature in Access.  If I can't do this
> > with the Chart option is there another way to have a bar appear on a 
> > report?
> > Thank you in advance for any help that will be offered. 
> 
> 
> .
> 
0
Utf
1/28/2010 7:24:04 PM
Ann,

What Steve is proposing is probably a little more coding.  Please post the 
SQL of the query you are using to create your chart.  Seems to me you could 
use the same unbound text boxes on the chart BUT it depends on the query 
which might need a little modfication or not...

-- 
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors 
II

http://www.regina-whipp.com/index_files/TipList.htm

"Ann" <Ann@discussions.microsoft.com> wrote in message 
news:E827EF51-3B2B-47FF-A95C-944217192287@microsoft.com...
I'm really rather lost by what you are explaining.  I don't understand how 
to
get a textbox to adjust it's size based on a number.  Plus, my calculations
were not done in the query but on the report in unbound textboxes that are 
in
the report footer.  Can you give me more detail?

"Steve" wrote:

> Since there are only four elements, I would not create a bar chart. I 
> would
> use a textbox for each element and adjust the width of each textbox to
> represent the percent of each. It would look like:
>                                     ____________
> Never                         [____________]    35%
>                                     ______
> Small Extent              [______]                  15%
>                                     __________
> Moderate Extent       [__________]         30%
>                                    ________
> Greate Extent           [________]              20%
>
> You can do all the calculations (count, percent, width) in a query. Note
> that percent would be shown in a separate textbox and its Left property
> would also need to be set by the calculated percent. Since all 
> calculations
> are done in a query, the display would aotomatically update as the data
> changed.
>
> Steve
> santus@penn.com
>
>
>
> "Ann" <Ann@discussions.microsoft.com> wrote in message
> news:6B8F2B94-9B67-47CF-AD14-FF19FFC0428B@microsoft.com...
> >I am using Access 2002.  I have a query with one field called
> > txtUsedKnowledge that has four possible responses.  They are Never, 
> > Small
> > Extent, Moderate Extent and Great Extent.
> >
> > I did many calculations with these including counting them individually
> > and
> > getting the percent for each one based on the over all count.  Now I 
> > would
> > like to create a bar chart with the four responses listed that will 
> > update
> > each time it's run.
> >
> > I created the chart but have to type in the figures for each response
> > myself.  Since the calculations are done on the report and not in the
> > query
> > is there a way to use the fields from the report.  Unfortunately, this 
> > is
> > my
> > first time trying to use the Chart feature in Access.  If I can't do 
> > this
> > with the Chart option is there another way to have a bar appear on a
> > report?
> > Thank you in advance for any help that will be offered.
>
>
> .
> 


0
Gina
1/28/2010 7:38:04 PM
You need to make the recordsource of your report a query. You then can do 
your calculations in the query.

<<how to get a textbox to adjust it's size based on a number>>
You need to determine what width you want for 100%. Say 4 inches. That's 4 x 
1440 = 5760 twips. Then say in your query you calculate Never percent to be 
35%. You set the width of the textbox with:
Me!Never.Width = .35 * 5760

Put that code in the Open event of your report.

Steve
santus@penn.com




"Ann" <Ann@discussions.microsoft.com> wrote in message 
news:E827EF51-3B2B-47FF-A95C-944217192287@microsoft.com...
> I'm really rather lost by what you are explaining.  I don't understand how 
> to
> get a textbox to adjust it's size based on a number.  Plus, my 
> calculations
> were not done in the query but on the report in unbound textboxes that are 
> in
> the report footer.  Can you give me more detail?
>
> "Steve" wrote:
>
>> Since there are only four elements, I would not create a bar chart. I 
>> would
>> use a textbox for each element and adjust the width of each textbox to
>> represent the percent of each. It would look like:
>>                                     ____________
>> Never                         [____________]    35%
>>                                     ______
>> Small Extent              [______]                  15%
>>                                     __________
>> Moderate Extent       [__________]         30%
>>                                    ________
>> Greate Extent           [________]              20%
>>
>> You can do all the calculations (count, percent, width) in a query. Note
>> that percent would be shown in a separate textbox and its Left property
>> would also need to be set by the calculated percent. Since all 
>> calculations
>> are done in a query, the display would aotomatically update as the data
>> changed.
>>
>> Steve
>> santus@penn.com
>>
>>
>>
>> "Ann" <Ann@discussions.microsoft.com> wrote in message
>> news:6B8F2B94-9B67-47CF-AD14-FF19FFC0428B@microsoft.com...
>> >I am using Access 2002.  I have a query with one field called
>> > txtUsedKnowledge that has four possible responses.  They are Never, 
>> > Small
>> > Extent, Moderate Extent and Great Extent.
>> >
>> > I did many calculations with these including counting them individually
>> > and
>> > getting the percent for each one based on the over all count.  Now I 
>> > would
>> > like to create a bar chart with the four responses listed that will 
>> > update
>> > each time it's run.
>> >
>> > I created the chart but have to type in the figures for each response
>> > myself.  Since the calculations are done on the report and not in the
>> > query
>> > is there a way to use the fields from the report.  Unfortunately, this 
>> > is
>> > my
>> > first time trying to use the Chart feature in Access.  If I can't do 
>> > this
>> > with the Chart option is there another way to have a bar appear on a
>> > report?
>> > Thank you in advance for any help that will be offered.
>>
>>
>> .
>> 


0
Steve
1/28/2010 7:48:19 PM
Reply:

Similar Artilces:

Ranking Chart
Hello I have to do a chart from a lot of different dealers. However, I only want the top 10 dealers to be on the chart and a sum of the rest as others. Is that possible? How could I do a tornado style chart? Thanks for your help -- cartera ------------------------------------------------------------------------ cartera's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=37669 View this thread: http://www.excelforum.com/showthread.php?threadid=572776 Hi, You will need to sort your data before it goes into the chart. You can then either manually select the to...

determining user-defined chart definition
How can I look at the code/description of a user-defined chart? ...

How to attach legend frame to chart frame?
I moved the legend of a chart inside the chart such thatthe legend fram aligns with the chart frame. However, it turns out that it onl *appears* to be aligned -- when I look at the print preview (or simpl print it) or when I export the whole chart to other applications suc as MS-Word, the two frames do not lign up at all. Likewise when I onl chage the zoom factor. Moreover, even within the Excel standard view, the alignment get distorted almost every time I enter and exit the chart (even when make no changes to the chart whatsoever). Is there any way to tell Excel to permanently attach the ...

Report Error
Hi all, any ideas on this.... We have created a number of Quotation Reports that work fine for all users except one ! When she runs the report she gets an error saying the sub report can be found. I have checked that she is in the same CRM groups as other users and checked that she is in the Reporting group in Windows/SQL - all looks fine. Any help would be appreciated Thanks Ian Hi Ian, Does she have problem with this report only or this happens to her other reports as well? Darren Liu, Microsoft CRM MVP http://www.crowecrm.com On Nov 7, 5:29 am, "Ian Kelsall" <...

Report Designer on Visual Studio 2005: Is it possible without 2003?
Do I have to install Visual Studio .NET 2003 to design new reports on CRM? I can't publish them on CRM when I have only the Visual Studio 2005 installed on my machine. When I install the SRS Designer, the Wizard tells me that I do not have the Visual Studio 2003. Thanks in advance! You need VS to create report rdl file. Visual Studio 2005 should work. To publish report you can log into CRM to upload your report file. "Juliano" <tom@jager.com> wrote in message news:B5808A28-946C-4F78-92DF-1C965F74448A@microsoft.com... > Do I have to install Visual Studio .NET 2...

Store Operations - Reports
I run this report in Store Operations Manager and selected to Show Column 'Profit Margin' and saved as a memorized report. Tried to run the report and although profit margin column appears alright, the Overall Profit Margin does not look correct. I opened the underlying file 'Sales - Detailed Sales Report.qrp' and discovered the following definition for the column with fieldname 'ProfitMargin' GroupMethod = groupmethodAverage I think that getting the Average of the individual items Profit Margin to arrive at the overall Profit Margin for the selected rep...

Reports Dictionary
We are in the process of upgrading to Ver10.0, we are currently at 8.0, if I point the reports to the Ver8 location what would be the outcome? If there is a problem how can I recover from this? Hi, What you've described is not recommended, as always create a backup of your db as well as GP client applications including all resource dictionaries.. As a best practice, please include exporting report package (GP and 3rd Party products) from release 8.0 to 10.0 as part of your upgrade exercise. The resulting package/s can be imported back in GP10's new reports diction...

One Command button for different reports
I have two controls, among others, on a particular form. One control ‘ContractNumber’ represents the specific customer (their phone number) and the ‘ContractType’ which represents the specific report. I have 5 reports, none of the reports are the same. Based on the ‘ContractNumber’ I would like to have a command button open the ‘ContractType’ for the particular customer. I realize I can have command buttons, one for each report and have a parameter query use the ‘ContractNumber’ to call up the specific report. I want one button to open different reports, say ‘Click to view customers C...

How to create "Space Bar hit" action setting?
Hello All, I have this slide with a long animation on a slide that is off mousclick transition. With a hit on the space bar the animation jumps to end. As the presentation is with mouse controlled navigation I do not want users to use the space bar (or they start hitting through the slide sequence) to end the animation. So, is there a way to 'end animation sequence' (like a space bar hit) as an action setting on an object? Thanx, Guurt ...

Jon Peltier
Jon, How would you do this with a Pivot Table? I'm not a Pivot Table novice and I just can't envision how this one would be done. Thanks, Barb Hi Barb - I didn't see this question at first, but I responded in the original thread. I didn't try to replicate the OP's one column setup from his first post, because it didn't match the more logical arrangement in his post in the other forum. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Barb Reinhardt wrote: > Jon, > ...

XL 2007 : Noty able to move data values in XY Scatter chart
Hi All, In Excel 2003 I can move data values (scattered points) in XY Scatter chart however this functionality is not working in Excel 2007. Any help? Tushar - Search Google for "excel 2007 deprecate chart" and one of the results is http://blogs.msdn.com/excel/archive/2006/08/28/724641.aspx which says "Direct Manipulation of Data Points on Charts In Previous versions of Excel, data points on a chart could be dragged, resulting in their source values being changed on the worksheet. This seldom used feature has been deprecated for Excel 2007." - Mike http://www...

Delete record(s) in other cells based on value of one cell.
What I have is a protected sheet, where all columns except for 3 are protected. I cannot delete the entire row with protection turned on. So, what I need is to be able to delete the value in these three columns, only when the value in column A is "d". Any clues? Protection must remain on, users are using this spreadsheet and I cannot allow them to have access to the formulas in these other columns. Help...Micheal Provide a macro that unprotects the worksheet, does the work and reprotects the worksheet is one way around the problem. MPope wrote: > > What I have is a ...

reports question #2
Hi How do I generate a report that will tell me how many store credits (or Gift Certificates) have been issued/redeemed in a givin time frame? I have a report "Detailed Sales with Tax & Tender" but the only problem is it does not show more than one tender type per transaction. So if a customer combines two tender types only one is shown. Thanks throw me a bone?...anyone cheers "christian" wrote: > Hi > > How do I generate a report that will tell me how many store credits (or Gift > Certificates) have been issued/redeemed in a givin time frame? &...

Discount Report #2
Good Day, Can someone assist me in obtaining a discount report that shows the dollar vale of the discount per transaction with a column header total? This will be greatly appreciated. I have tried to tailor the Detailed Sales report but i can not get the discount dollar value . Thanks in advance... Don add this column to the end of your sales report: Begin Column FieldName = "discount" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "$ discounted" VBDataType = vbCurrency Formula = &qu...

Change left bar and top bar names on Order screen
Ok, I guess this could be the left bar and top bar names for any screen, but I'm specifically asking about the Order screen. I have a client that wants to change "Create Invoice" at the top to "Close". They also want to change "Write-In Products" to "Write-In Rooms". Where do I change these? I don't see them as options in the customization screens, even in the N:1 and 1:N areas. On Sep 23, 2:18=A0pm, Wayne Walton <waynek...@gmail.com> wrote: > Ok, I guess this could be the left bar and top bar names for any > screen, but I'm ...

Charts: want to assign fixed color for each slice
Hi folks, I am using excel 2000. Am creating two pie charts. Chart 1 contains six people, Chart 2 contains 3 people. Let's say the Excel default color sequence is: Red, Blue, Yellow, Purple, Green, Pink Chart 1 will be: Alex Red Billy Blue Charles Yellow David Purple Edward Green Frank Pink Chart 2 will be: Billy Red Charles Blue Frank Yellow However, this is not what i want. How can I assign 1 color to each person... so that Billy is always = blue, while Charles is always = yellow. and Frank is always = pink. Thanks a lot. cheers Jon Peltier has instructions at the end of his p...

How do I show yyyy only on x axis of a chart?
I did not use a formula for the date (4-digit year only) in column A, I manually entered the years. When I attempt to create a chart, the years either show up as 1, 2, 3, etc or as m/d/yyyy with the year showing in all instances as 1900. Can you help me enter the yyyy only on the x axis of the chart? Unfortunately there is no option for yyyy in "format cells>date". Hi, The answer depends on the version of Excel you are using. 2003: Select the chart and choose Chart, Source Data, Series tab, and delete the Year series from the Series box. Then select the Category (X) ...

General Protection Fault on Game with NT-Based systems
Hello all. I'm having some trouble with a game running on NT-based windows OS's. It keeps throwing a general protection fault from a DLL. Is there any way to solve this issue so the game will run on modern windows OS's? I can get the game to run on Windows 98SE. So far I've failed to get it to run on Windows XP Pro and Windows 7 Pro. I'm currently trying to get a copy of Windows 2000 and Windows ME so I can try it with those two systems. Compatibility mode failed. I can't figure out if there's a way to get it to work or if it can even work at all. I...

Chart and outlook
Can I send an Email message to Outlook whenever a chart value goes outside a min or max value Sure. Depends on how comfortable you are with VBA programming -- especially inter-program communication and writing event-driven code. -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <0d6a01c38cb8$63752ac0$a301280a@phx.gbl>, Phillip.Human@sasol.com says... > Can I send an Email message to Outlook whenever a chart > value goes outside a min or max value > ...

Font color of status bar pane
Is it possible the change the font color for only one pane of the status bar (instead of the whole status bar)? This will be helpful: http://www.codeguru.com/Cpp/controls/statusbar/article.php/c2985 Look for more samples at codeguru and codeproject. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "mercy l." <anonymous@discussions.microsoft.com> wrote in message news:1DEED502-D0E9-44FD-A35E-00B9A1296BD3@microsoft.com... > Is it possible the change the font color for only one pane of the status bar (instead of the whole status bar)? ...

Case Reports; Average Case Turnaround Time and Number of Cases by
Case Reports; Average Case Turnaround Time and Number of Cases by Owner Y axis only shows zeroes even when bar chart represents a value and owner has multiple cases logged??? Does anyone know why this is happening and if there is a fix? "Sorcha" wrote: > Case Reports; Average Case Turnaround Time and Number of Cases by Owner > > Y axis only shows zeroes even when bar chart represents a value and owner > has multiple cases logged??? Hi Sorcha, I'd like to understand exactly what you're seeing and why this is happening and have a few questions. Have you c...

Can we make a document frame window title bar disapper?
Hi, friends, I registered a document template in InitInstance(). It may not be an MDI child. When I created a new document, I tried to make the frame title bar disappear by setting: cs.style = WS_DLGFRAME; in function PreCreateWindow(CREATESTRUCT& cs). Unfortunately, the title bar was still there, although no more caption. I also tried setting: cs.style &= ~WS_CAPTION; and some other combinations, no luck. Could anyone tell me how to do it? Or, can we do it? Thanks. You need to remove WS_CAPTION style. You can do it in Precreatewindow and for mainframe do it after it ha...

Bar Chart #5
I would like to do a column chart that groups the bars representing the data points adjacently, for example: 3/2006 sales and 3/2007 sales / 4/2006 sales and 4/2007 sales, etc. Any ideas? Just place the data in separate columns, on for each year, select the data and create the graph. Bob Flanagan Macro Systems http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Kathy" <Kathy@discussions.microsoft.com> wrote in message news:11EA5885-385A-4E98-83D1-4BCA7FC137EB@microsoft.com... >I would like to do a column chart that groups the ...

what is the fast(est) way to calculate millisec time span in mfc
hello board, I have an application that receives about 10 Kbytes/Sec from serial port in 8 byte packets. packets have to be labelled with time. I couldn't add time label within device because it would need much wider communication bandwidth, so windows has to do it. I ususally write my programs using mfc. Now I want to add a time label to each packet with 1 millisec resolution. I don't want to add a timer because handling timer messages need an unnecessay computational overhead. I like to save last time label and when new packet arrived I calculate elapsed time and add the elap...

Vary colors in area chart by x-axis value
I have an area chart that displays electricity usage (y-axis) for each hour of one day (x-axis). I want hours 1-7 to be blue, 8-12 green, etc. -- Deirdre M. Hi Deirdre, There is a 'Conditional Chart Formatting' sample workbook at http://edferrero.m6.net/charting.aspx Ed Ferrero http://edferrero.m6.net/ >I have an area chart that displays electricity usage (y-axis) for each hour > of one day (x-axis). I want hours 1-7 to be blue, 8-12 green, etc. > > -- > Deirdre M. ...