Forms :: Pivot Chart To Show Progress In Multiple Projects Over Time
Aug 30, 2013
I'm aiming to create a stacked area chart to display the progress of multiple projects over time, so we can review and forecast project load over time.
The data comes from this table:
tblProject
* ProjectID
* DateProspect
* DateConfirmed
* DateStarted
* DateComplete
A project passes through the statuses Prospect, Confirmed, Started, Complete. The current status of each is calculated in qryStatus from the dates in tblProject. The past and forecast statuses for each project will probably be calculated each time the chart is created (or maybe not necessarily so?)
To create the stacked area chart, the x-axis will be months (e.g. Sept 2013 - July 2015). On the y-axis will be the count of ProjectID, and the series will be the different project statuses.
The sticking point is getting from the Date* fields to the past and forecast statuses in each month. After that I imagine it'll be reasonably straightforward to put into a pivot/chart.
View Replies
ADVERTISEMENT
Aug 27, 2014
How do I change the legend labels of a pivot chart form so that it doesn't show the words "sum of" in front of my description. I am using access 2007.
View 2 Replies
View Related
Oct 11, 2013
I am trying to publish an Excel Pivot Chart, but keep getting Run Time Error 1004 - Application-defined or object-defined error...
I have tried the code 2 ways:
Code:
Sub PublishChart() Worksheets("Historical_Pivot").Activate
With ActiveWorkbook.PublishObjects.Add(xlSourceChart, _
Path & "" & "AWA" & "_" & "JAMALCO" & "_" & "Chart" & ".htm", _
"Historical_Pivot", "JAMALCO", xlHtmlStatic, "ABC REQ BACKLOG_2013", "")
.Publish (True)
.AutoRepublish = False
End With
End Sub
Code:
Sub PublishChart()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbookSet ws = wb.Sheets("Historical_Pivot")
ws.ChartObjects("JAMALCO").Activate
With wb.PublishObjects.Add(xlSourceChart, Path & "" & "AWA" & "_" & "JAMALCO" & "_" & "Chart" & ".htm", ws _
, "", xlHtmlStatic, "", "")
.Publish (True)
End With
End Sub
View 1 Replies
View Related
May 9, 2014
I am writing in access 2007 and want to make a trend analysis chart based upon performance date.
I have created a query using 3 criteria from a table - date, client and performance percentage.
I've then put this into a pivotchart no problem BUT as some clients have more than one entry per day its giving me figures over 100%.
Is there a way to exclude multiple entries for any given date while retaining the other criteria ?
View 1 Replies
View Related
Sep 13, 2014
I've attached a rudimentary example db to give an example of what I'm trying to achieve.
There is a form on the db called Resourcing with several combos.
When I choose a Training_Type from the first combo it automatically filters the Project_Title combo to show only the projects that match that training type. When I choose a Trainer_Name the text box for team automatically completes.
The bit I'm struggling with is Trainer_Name. As you will see from the table Course_Details not every trainer can deliver every Project_Title. So what I want is to display only the trainers associated with the project title chosen rather than displaying the whole team in the Trainer_Name combo.
Someone suggested a junction table but I'm not sure how this would answer my question. I'm thinking some sort of Dlookup but don't know how I would write it when it has to include data from four fields.
View 14 Replies
View Related
Oct 16, 2013
We have a chart that is showing jobs by customer. I'd prefer it be in a pie chart but it's way too many to read. We changed it to a bar which is much easier to read but it displays the percentages as 100% for everything.
View 5 Replies
View Related
Jul 17, 2013
I have a pivot chart that has site locations, and hours on the bottom, and then count of records with each site. Which is how many people used this site during this time for each bar. My problem is when I generate my pivot chart, every single time I have to fix the scale on the right hand side because it has decimal points in it. They only appear when I add dates/times to it.I want to give this to my boss that has access runtime, so all he has to do is click the button to get the chart and then print or send it off. How do I get rid of those decimal points when the report is generated?
View 1 Replies
View Related
Mar 22, 2013
I made a form pivot chart. When I open it by going to forms and opening, it opens in the pivot chart view. However, I created a button on a switchboard form for easy access to this pivot chart form and when it opens from the button it is in form view and not pivot chart view. How can I get it to open in pivot chart view from this button so that the users don't have to keep switching it to pivot chart view manually?
View 1 Replies
View Related
Jun 28, 2013
I have a pivotchart subform who's Filter Property I've set to:
Code:
[EEIC_ID] In ([Forms]![MainDataControl].[EEICBuffer],0) And
IIf([Forms]![MainDataControl].[TypeIDBuffer]="",[AG_TYPE_ID] Like "*",[AG_TYPE_ID] In ([Forms]![MainDataControl].[TypeIDBuffer],"")) And
IIf([Forms]![MainDataControl].[AgencyBuffer]="",[AGENCY_ID] Like "*",[AGENCY_ID] In ([Forms]![MainDataControl].[AgencyBuffer],"")) And
IIf([Forms]![MainDataControl].[FacilityBuffer]="",[FACILITY_ID] Like "*",[FACILITY_ID] In ([Forms]![MainDataControl].[FacilityBuffer],""))
The issue is that none of the parameters are recognizing the textbox controls even though the references appear to be correct. Per access.mvps.org/ access/forms/frm0031.htm, I've attempted several versions of the syntax.
I believe that I've done this successfully in several other forms, though none have been been via a pivotchart. What's more, this exact same filter string worked when the pivotchart was its own popup and the referenced form was a separate window. Once I embedded it (being the pivotchart) as a subform, however, I began to be presented with the "Enter Parameter Value" dialog, even though the control's address had not changed. Note: the form with the embedded pivotchart is separate from the control's parent form.
In essence: the filter property of a pivotchart subform has a parameter that references a control on a seperate popup form, and every one of the syntactual statements I've tried returns an "Enter Parameter Value" dialog (where they did not when the pivotchart was not a subform). I've also attempted to isolate this issue by putting the text box controls on the parent form (rather than the separate popup form) and referencing them via the pivotchart subform, but this provided no resolution (the same thing happened).
View 8 Replies
View Related
Jan 20, 2014
I am trying to change dynamically the text of the PivotChart, according to the current filter. This is a user request. The VBA code should run whenever the view is changed to pivotchart. I'm im Access 2007
This is my code so far:
Private Sub Form_AfterFinalRender(ByVal drawObject As Object)
If Not (Me.CurrentView = acCurViewPivotChart) Then Exit Sub
Me.ChartSpace.HasChartSpaceTitle = True
Me.ChartSpace.ChartSpaceTitle.Caption = Me.Filter
End Sub
This generates an error:
Runtime error '-2147467259 (80004005)':
Cannot change chart attributes in an event handler
I am stuck. Is there any way around? I have placed a button to the datasheet view of the form to take users to PivotChart view. I can change the chart title from the code of that button. But I can't prevent users from using the built in ribbon button for pivotchart view, so I need to run this code from an event.
View 3 Replies
View Related
Jun 19, 2014
I have a pivot chart based on a crosstab query. I would like the items on the category axis (x-axis) to appear left to right in the order that they appear in the query results.
Some specifics:
Tables:
tblFreq
FreqID (PK, Number, Range 1-7)
Frequency (text)
tblResp
RespID (PK,Number, Range 1-5)
Response (text)
tblResults
ResultID (PK,AN)
FreqID_FK
RespID_FK
Query:
TRANSFORM CInt(Nz(Count(tblResults.ResultID),0)) AS CountOfResultID
SELECT tblFreq.Frequency
FROM tblResp INNER JOIN (tblFreq INNER JOIN tblResults ON tblFreq.FreqID = tblResults.FreqID_FK) ON tblResp.RespID = tblResults.RespID_FK
GROUP BY tblFreq.Frequency, tblFreq.FreqID, tblFreq.FreqID
ORDER BY tblFreq.FreqID
PIVOT tblResp.Response;
[code]...
Which I suppose is alphabetically ordered.I am unable to use OrderBy in the forms property sheet because tblFreq.FreqID is not an available field, even though it's an expression in the query.
View 3 Replies
View Related
Sep 16, 2014
I have a form, a couple of comboboxes and text boxes on it. When these are filled out, the SQL of a query is changed using these parameters.
There are three subforms on the form, all pivot charts, all based on the query being changed.
The goal would be to update all three according to the user-given parameters.
Right now the subforms only update if I close and open the form, which is probably not the best solution, since it's too slow.
I've also tried to requery and refresh them, with no result.
Then I tried to overwrite the recordsource of the subforms with the same text that was originally there. This got them to refresh their data, but then all of the charts disappeared and had to be built again, so this is a no go too.
View 7 Replies
View Related
Nov 18, 2013
I have a need to display a pivot chart by clicking a button on my menu screen. At the moment my button will just display the query results and if I then click on View/PivotChart View my chart displays.
Code:
Private Sub Command316_Click()
DoCmd.OpenQuery "R06X - OOS Chart", avViewNormal
End Sub
View 4 Replies
View Related
Feb 19, 2014
I am completely new to Pivot Tables/Charts?
Basically i have two totals:
Value 1 which is the number of working Employees
and Value 2 which is the total number of Employees
Both are subject to change but my main problem is that Value 2 (Total of Employees) i want to display as 100% in a chart and Value 1 is the percentage of employees utilized.
I have the calculation working fine (Value 1/Value 2).
But if for example i have 35 employees as Value 2, how do i make that show as 100% in the chart?
I get things like 35 or 3500%?
I have played about endlessly changing values etc but just cant get it.
View 3 Replies
View Related
Dec 15, 2012
I need to create a Pivot Chart using the data from more than one table.
The main table has fields including 'Job name', 'task name' and 'hours worked'.
The second table (linked to the first by 'task name') has a field 'allocated hours'.
I would like to plot the 'total time worked' against 'allocated hours' (in a stacked bar style chart), in order to monitor time usage.
View 2 Replies
View Related
Dec 13, 2013
I have chart (grpMaster) that is embedded in a form (frmMain), the chart is a line graph based on a query (qryDataFilter) with 4 data series.
What I want to do is have various checkboxes (chk1 to chk4) that if selected show the series and if no don't - It seems straight forward!
I've tried SeriesCollection() but this comes up with errors. (Object doesn't support this property or Method).
The easiest way i can think of is just to hide / show the series line on the chart, but can't figure this out?
View 4 Replies
View Related
Mar 28, 2008
Hello there,
I need help regarding how to use two queries in one pivot chart
I have 2 queries one for placed status and other for Not Placed Status
View 2 Replies
View Related
Aug 14, 2006
Hi,
I have table (Table A) whose column (Column A) is a lookup table linked to Table B. Column A's type is of number. Table B has 2 columns. Column B1 is of type autonumber (set as primary). Column B2 is of type text. When I am populating Column A, I select from a drop down list which shows the values of Column B2. Column A will populate with what appears to be Column B2 values. However, whenever I create a chart with Column A, the values in the legend always show the numeric value of Column A, not the values of Column B2 as I want. This occurs in pivot charts as well. Here is an example:
In this case
Table A = A_Incident_Main_Table
Column A = A_Incident_Main_Table.System_Name
TRANSFORM Count(*) AS [Count]
SELECT (Format([Date],"mmm"" '""yy")) AS Expr1
FROM A_Incident_Main_Table
GROUP BY (Year([Date])*12+Month([Date])-1), (Format([Date],"mmm"" '""yy"))
PIVOT A_Incident_Main_Table.System_Name;
Here is another example:
SELECT A_Incident_Main_Table.System_Name, Count(*) AS [Count]
FROM A_Incident_Main_Table
GROUP BY A_Incident_Main_Table.System_Name;
A_Incident_Main_Table.System_Name is poplulated in with values from the lookup table (Table B). In both examples, I see only the numeric values in the legend, not the text values of Table B.
Any ideas on how to get around this?
View 1 Replies
View Related
Jan 24, 2015
I am using 2013 access and i cannot find a chart details i.e. pivot or bar charts. Therefore I have exported the information into an excel . if there is no charts functions on this access .
Is it possible to export the information yet the pivot chart stays in the excel so i don't have to keep creating the charts information.
View 2 Replies
View Related
May 29, 2015
I have a problem with seting up color for my pivot chart. First I set up color I want(pic 1).but then when I use filter for End Customer all the colors will revert back to default settings (pic2).Is there a way how to force it so it sticks with colors I chose? VBA code?
View 8 Replies
View Related
Jun 28, 2014
I have a form that have 9 subforms. every subform show pivotchart.
i was trying to export these chart using this code
Code:
Form_qCFA_Counter_RRC_Rei_IuB_Base.ChartSpace.exportpicture "D:iubbase.jpg", "JPG"
Form_qCFA_Counter_RRC_Rei_Power_Base.ChartSpace.exportpicture "D:Power.jpg", "JPG"
Form_qCFA_Counter_RRC_REJ_Code_Base.ChartSpace.exportpicture "D:code.jpg", "JPG"
Form_qCFA_Counter_RRC_Rej_Other.ChartSpace.exportpicture "D:other.jpg", "JPG"
Form_qCFA_Counter_Fail_CSPS_Code_Cong.ChartSpace.exportpicture "D:codecong.jpg", "JPG"
Form_qCFA_Counter_due_to_ce_cong.ChartSpace.exportpicture "D:cecong.jpg", "JPG"
It work but the image only show only 2 or 3 images, other chart only show nothing. is there something that i missed ? How to export all these chart (9 chart) as image without corrupting ?
View 3 Replies
View Related
Nov 7, 2012
I have made a database where i plan the company's resources in form of hours. The idea is that every employee will put in their own data for the upcoming 4 weeks. I do this in a table where i store employeeID, weeknumber, year, PlanneHoursUsed, ActualHoursUsed.
This again i take into a pivot chart to see a forecast for the next month.
I have somehow achieved to get up a graph where i for every month can see the sum of all planned hours and all used hours (See attachment). BUT; my boss wants to see a running total instead of a "weekly image". On the graph attached one can see the numbers for every week. But i would want to see instead a running total.. For instance week1 then week2 would be the sum of 1 and 2, week 3 would be 1 ad 2 and 3 and so on. So the graph would be inclining throughout the year.
Is this possible with functions with my current data? Or do i need to have another field in my table where i store a running total? (This would be tricky when we get a lot of data and when somebody changes an old value...)
View 2 Replies
View Related
Jul 11, 2014
I have a query that has a pivot chart, i need to be able to save this as an image so i can insert it into a html email but i cannot find anything, I am using access 2010 ...
View 3 Replies
View Related
May 21, 2014
I am having a terrible time getting this to work. I have a mainform that contains 11 multi-list boxes. That mainform when I select whatever I want in any of the 11, select all items in each of the 11, or select nothing and click my show results works great. In my detail section it displays the information it should. I need to now take that and put it in a report with a pivot graph. I created a subform that is my pivotgraph and the reason for a subform is the end-user still wants the ability to filter more should they choose.
In addition, the regular graph you can put in the report does not allow me to put in multiple items. For instance I need to have sumofmbrstargeted and sumofmbrsconverted in the values and it will say I can only choose 1 value and then it will say up to 6 items and I have a 8. So, I opted for a subformpivotchart. If I create the following code, the subform updates based on the filters on this mainform everytime. Works like a charm.
Code:
Private Sub cmdGetGraph_Click()
DoCmd.OpenForm "Graph", acFormPivotChart, , GetFilterFromListBoxes
End Sub
The problem is, I want the Graph in my Report. If I take the Report and do the same basic thing:
Code:
Private Sub cmdGetReport_Click()
DoCmd.OpenReport "Search", acViewPreview, , GetFilterFromListBoxes
End Sub
It asks for the parameters again. Example, LOB, Plan, Prod_NM. I tried just disregarding the report and created yet another subform for the reporting piece and embedded the graph and that still asked for the parameters. It works great for the subform as the graph and I want to have the ability to use the GetFilterFromListBoxes, apply it to the Search Report with the embedded Graph.
View 14 Replies
View Related
Feb 16, 2015
I have a pivot chart in ms access and i want to have auto-axis for y-scale. I want that when the data will be changed the scale would be automatically arranged.
View 14 Replies
View Related
Dec 2, 2014
I have a bar pivot chart made in a form this load fine, I have the below VBA code in place to control the bar color depending on what the bar show.
Set frms = Forms![OtherCases created by Indv by Month chart].Form.ChartSpace.Charts(0)
Dim i As Long
With frms
For i = 0 To Forms![OtherCases created by Indv by Month chart].Form.ChartSpace.Charts(0).SeriesCollection.Count - 1
With .SeriesCollection(i).Name
Select Case Forms![OtherCases created by Indv by Month
[code].....
When I then open the Report this is not working at all each bar just get a random color regardless what the bar show.how to convert the above to fit a Pivot chart form a form that is displayed on a report.
View 1 Replies
View Related
