Charts From Queries - 6 Fields!!!!!???? Is That It!!!!???
Oct 17, 2007
I am infuriated with MS Access now!!!!!
I am building a nice little system and now I am developing the reporting features. I want the results of a query to populate a chart.
Basically it is a monthly chart, so would have 12 months along the x axis and a quantity on the Y. A simple line chart!
I want 4 different lines each representing a form of occurrence. However Access limits you to using a maximum of 6 fields! Are you serious!!! I need 12 just for the months!!!!
Is this a serious feature of Access or am I being stupid?
I know many people who export a lot of stuff to Excel for charting, and now I know why. Is this my only option? I wanted to avoid multiple applications, but if I have to, will have to learn how to automate to an excel template, some thing I am not aware of how to do currently and am limited in my VB programming abilities.
I cannot believe that a powerful tool such as Access could be sooooo limiting in it's reporting capabilities!
Working on a report that displays multiple pie charts. Each chart is based on a different query. I cannot pull a single query for all charts due to the criteria for each conflicting with each other. Each query is filtered by Fiscal Year based on what I input into my Fiscal Year Filter form. The command button on the FY Filter Form opens the report, set TempVar to the FY field (the criteria for each query), and closed the FY Filter Form. This works as I want it to.
The problem: I have additional fields I want to show up on the form such as number of completed students (WINGED). This number is based on yet another query where all completed students are counted [WingedCount]. I have tried to write an expression to an unbound field that points at this [WingedCount] field but it does not work. Then, I changed the report's source as the query with the [WingedCount] field. This does work, however this is where I run into an issue
I open my FY Filter Form and type in my criteria and select the open command button. However, now I am asked for the criteria again for each chart on the report.
I need a way to input the criteria only once and have all charts populate as well as my count field.
I have attached a jpeg of my current report and will upload jpegs of the needed output following this post.
I've created some queries on some data that return summary information to then chart over time. The fields being returned are YearGroup, Academic Year, Term, TotalI want the data to get sorted by YearGroup (Year7, Year8, Year9 etc) however, when it sorts the data it sorts it starting with Year10, Year11, Year7, Year8, Year9.
I work at a program where we have individuals try out for a field (coming from various backgrounds). The program has been in place since 2012, and I wanted to start looking at the data for analysis and comparing trends, etc; I imported our master file (excel) into Access. It's in one big table, column headings include roster number, name, gender, type, FY (fiscal year), and finalcode (Select, Non-Select - but put it in as 1 or 2 in the field)).
I created a query, and was able to get data I was looking for.I then set it to Pivot Chart (bar graph).
Bottom (Believe its X) Axis: I have Type, Year. Right Side of Chart (TypeCodeNumber) Top of Chart: Count of CodeNumber
It gives me the totals (whether select or non-select) just fine, but I have been racking my brains, watched tons of YouTube videos and read a lot on how to make it give me a percent. I found a way to go Percent on the left side of the bar chart, but the Values go up to say 3300%. I know the problem is with the Count up top.
I would like to create a statistical cusum chart in my db, I can do it in Excel but it's quite difficult to do in Access. Does anyone know how to do one, or know of any SPC add ons for Access (there are loads for Excel)?
I would like to create a report from one of my existing queries. I would like this report to have a pie chart on it showing results from a field called concerns in the querie.
I would like the chart to break down the amount of each concern listed i.e. 4 of this 3 of that 5 of those etc.
i use the chart wizard to pull in the field data and when I click finish, it gives me basically nothing. Any ideas?
I solved one problem only to hit another. I have several charts that work off of a query. The several charts all compare various variables to year. I created the charts and they all work great. However, the user now wants to be able to select or compare various years as opposed to seeing them all in every chart. I made the year an integer. I then tested the query that all the charts are based on to see if it filters properly.
In the query's date filed I have the criteria Like [Enter the desired year] This works fine if I am just looking at the query. However, if I pull up the chart that the query is based on I get the error message, "The microsoft jet database engine does not recognize '[Enter the desired year]'as a valid field name or expression."
I have wasted an enormous amount of time simply trying to filter my charts by year. A single field in my table that is an integer with 0 decimal places. All the dates are entered as a 4 digit number. Ex 2006.
Any help / suggestions would be greatly appreciated.
My end desire is to have a form with two text boxed named "start" and "end" in addition to a number of butons each corresponding to one of the charts. My user will enter two dates, one in each of the text boxes "start" and "end" When he/she presses one of the chart butons, the corresponding chart will open up showing only the two (or) one selected years' data. This would be filtered by the query that all of the charts are based on that has a set criteria, "Like [forms].[charts].[start] or like [forms].[charts].[end]"
As I mentioned earlier, I already have this all set up. The query works fine based on all of the above. However, if I click the butons or try to open a chart I get the message stated above.
Thanks again to all here at the forum for your invaluable help.
I'm trying to set up a database to track a huge group of corporations. There is the parent company, then a bunch of subsidiaries below it. Some subsidiaries have another layer of multiple subsidiaries below it, some have none. Any thoughts as to how to do it???
I am adding a chart to my switchboard page which comes from a query...does anyone know if this is ok to do or if its not recommended. The Chart takes the database and summarizes its contents..
Didnt know if performance issues would come into play...
Hi, I have done a search before posting but cannot find what I am looking for.
At work I use a database to record incidents (work that I do). I have written a query for incidents for 2007 and 2008 and I can do a line graph for each year showing the monthly incidents by doing Insert, Form, Chart Wizard
What I am trying to do is a line graph for 2007 and 2008 together to compare performance but i can only ever choose one query for my graph, either 2007 or 2008.
Is there a way I can get both years on one graph so as i can compare performance.
I am trying to create a chart on a Form. In Excel I have no problems arranging my data. This is my first try for charts in Access. I have a table. I created a query summing two fields of this table:
Total Workers Total Workers: DCount("[WorkerNameLast]","1-StationVerifyTbl","[ClockNbr]<>'*'") Total Workers = 120
Total Verified Verified Workers: DCount("[WorkerNameLast]","1-StationVerifyTbl","[StaPrimary]=True") Total Verified = 43
So far so good. That is the correct information. What I want in a Chart is two bars side by side. One bar at 120 and the other bar at 43. I have tried everything and cannot get this to work. I am getting confused on the Axis, Data, & Series. I think I need another Field somehow. The totals I would call Monthly Totals
Could someone please help me get this thing started. I have wasted a week and feel like I have learned nothing except how to count in the Query.
After this I would like to be able to show a percentage of those verified. I have 120 workers and 43 are verified. That would be approx 35%. Anyway I am assuming that there is a solution and that I am just floundering for nothing. Thanks in advance.
a co-worker set up several pivot charts in queries, that we now need to get into a distributable format. I had envisioned having these in a report, but access doesn't agree. Is there any way of getting the chart out of access, intact, to place in a word or power point file, or into a report? My attempts to get the chart into a report have all ended with the table-like grid coming up. Surely there's a way... thanks!
CAN THE FORMAT OFF THE WEEK NO USED IN PIVOT CHARTS BE CHANGED THE DEFAULT WEEK NUMBERING SYSTEM IS NOT COMPATIBLE WITH THE ENGLISH WEEK NUMBERING SYSTEM. I CAN WORK AROUND THE PROBLEM IN VBA CODE BY USING DATEPART("WW",DATE,VBMONDAY,vbFIRSTFOURDAYS) WHICH RETURNS WEEK NO IN LINE WITH THOSE USED IN ENGLAND BUT THE PIVOT CHART DOSEN'T SEEM TO OFFER THE OPTION OF WHICH WEEK NUMBERING SYSTEM TO BE USED ANY IDEAS!
I have a bunch of charts in my forms, and my boss wants to be able to adjust axes. I have added a context menu that brings up a little form and takes values that are then thrown into the graph by way of a function. URL....Here's the current code:
Code: Public Function AdjustTREFAxis() 'See form fTREFaxis Dim objChart1 As Object Dim objAxis1 As Object
[code]....
notes: -ignore the global vars, they are a temporary measure. -"Graph19" I know, not good. I never expected to call it in code though , I'll fix it later -the while loop keeps the rest of the code from running until the axes have been submitted.
Ok, so this code works for the x and y1 axes. I assumed that Axes(3) would iterate to the Y2 axis, it does not...I also found that after you change the axis the changes are persistent for the graph even upon repaint/requerying it. This is problematic and I need a way to set an On Load event that resets the graph to "autoscale"
Lacking that, how to access the Y2 axis, or how to reset the axes to autoscale that would be keen.
I have a couple charts that I would like to show on my switchboard. Right now I only have one that is up everytime I open my database. Is there anyway to have it do a random Sub form show up everytime you open the database up? Thank you for your help. Without this forum I would have shot myself a long time ago!
I recently started using the Chart feature in access. Everytime the form with the graph loads up windows installer pops up and asks me to install. Since i let it install like 3 times, i click cancel and it still works fine. Anyone else have a problem like this? How can i prevent it from installing everything, since its already installed?
I have a simple table with fields Date, Parameter, Value. On my form I have a Parameter listbox and a chart. Once a parameter is selected I want to display the chart which shows all the values for the parameter by date. Then I want the user to be able to select a different parameter have the chart display the values by date, etc. Sounds easy. It's driving me nuts. The only way I've been able to do anything close to what I want is by linking the chart to the Parameter field in the table and using the Record Selector button on the bottom of the form and of course this is a totally unacceptable solution. All suggestions will be much appreciated.
I have created a pivot chart using the Autoform and now i want to export this charts to powerpoint, i cannot copy that chart which i have created in in autoform, i can export the pivot table to excel sheet and draw again the chats but i cant copy or export the pivot charts directly, Is there any option for that. can anybody help me in this case?
I need a basic string graph over time recieved from a cross tab query. The data points all come in fine, but the "strings" / lines connecting the points do not show up.
I believe the reason is because there are just too many records.
I culled the data down using a vba loop to only show 1 stop per minute. (BTW, is there a way to do this using only SQL?) I now show all record points, but still do not show the lines between points. Some of the lines show up, but the more data points on the graph there are, the less the lines show up, till only one or two show.
This is an Access limitation that I'm unsure of how to get around. This really shouldn't be an issue though, because Excel handles the graph just fine EVEN BEFORE the culling of the data down to access acceptable limits. And it shows the lines between each record as well.
I have a nice chart that hasd done what I wanted it too.
Now I want to make the amount of X-AXIS data variable. Typically this would be 64 points. If you look at the datasheet, I can manually set the number of points by making sure the row selector is "raised".
Now that's say there is only 16 points of data to control. I can plot that those points but the chart is still 64 points wide with no data for points 17-64.
How can I, through code, prevent points 17-64 from plotting and thereby expand the graph horizontally larger and not display those null points?
I have tried a number of things including: objDataSheet.Row(17).Delete Graph_Data.Range("A1:A16:) etc...
Nothing I have tried has worked. Is what I need to do possible?
I have set up some pivot charts in Access 2003 that open up on the click of a button. No problems. My problem now is that several PC at work are running Access 2000. Is there a way of easily getting this to work????Many thanks Eq
Basically I have chart in a report thats based on a query that counts the amount of entries per month between two dates inputted by the user.
It all works fine but the chart that is based on the query only shows months that have an entry.
Eg if it counts all dates between the two dates and say the only month that has an entry is July, the chart will only show July. What I want is the other months to show (Null values) as zero, so every month shows. I'm probably missing something basic but can anyone help?
[TextPriDate] is the start date [TextPriDate2] is the end date
This is the query code (QryDate) SELECT tblMain.ID1, tblMain.Dt FROM tblMain WHERE (((tblMain.Dt) Between [Forms]![frmSwitchboard]![TextPriDate] And [Forms]![frmSwitchboard]![TextPriDate2]));
This the code from the chart in the Report SELECT (Format([Dt],"MMM 'YY")) AS Expr1, Count(*) AS [Count] FROM QryDate GROUP BY (Format([Dt],"MMM 'YY")), (Year([Dt])*12+Month([Dt])-1);
