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!
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?
View 2 Replies View RelatedCAN 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!
View 2 Replies View RelatedI 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
View 1 Replies View RelatedWhen I start my database normally, I'm not able to add fields, change chart type, etc, by right clicking in a pivot chart or table. It works when I open the database while holding down the shift key though, so I suppose there is an option in my database somewhere that I have changed, but which one.
View 1 Replies View RelatedI 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 am having trouble arranging my data from a record row into a query that will give me columns for the pivot charts that I need. My guess is that I need some sort of a complicated SQL statement, and I know almost nothing about SQL. I have expressed the problem better, and with graphics, here: http://www.olypen.com/bhardin/RGB/RGB.htm. I would love to hear the solution to this aggravating :mad: problem.
View 4 Replies View RelatedI'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???
View 1 Replies View RelatedI 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!
View 1 Replies View RelatedHi, okay completely new to access. I'm trying to create a chart based on a query with 1 record 8 fields. I can not figure out a way to rearrange the data into two fields 8 records.
The background is I have a hundred units, each with 8 wheels. I have a table with the following fields.
-Unit part number
-Diameter of wheel 1
-Diameter of wheel 2
-Diameter of wheel 3
-Diameter of wheel 4
-Diameter of wheel 5
-Diameter of wheel 6
-Diameter of wheel 7
-Diameter of wheel 8
So, the query tells me how many wheel diameters between a certain range, with the following fields:
- Diameter >530
- 530> Diameter >540
- 540> Diameter >550
- 550> Diameter >560
- 560> Diameter >570
- 570> Diameter >580
- 580> Diameter >590
- 590> Diameter >600
Now I want to plot the range on the x axis and # wheels in that range on the y axis of a bar chart.
Any suggestions on how I can achieve this? How do I create the diameter range as a record rather than a field?
Many thanks!!!
I have an application in Access 2003. It uses VBA to open an Excel file. The file has one tab for charts and one for data. The program counts how many charts needs to be created, one per project. It then will place 4 charts per tab, creating as many chart tabs as necessary, keep one data tab. The program then writes the data and links the data to the corresponding chart. It will also write legends, and scale the charts. This all works fine under Access 2003/Excel 2003.The error I get is "Application-defined error or object defined error".The line that gives me the error is: Set chCopy = xlsheet.ChartObjects(idx).I am using the Multi-Chart option frmChartType = 1
Now I'm moving to the Office 2010 world. It is not working. Below is the code I run to do the magic. I guess something is happening in Excel 2010 that is different from 2003.I found when I comment out the error line, and other lines dependent on chCopy it creates the charts, but all the charts are pictures and not charts!!
Code:
Public Sub GenerateChart()
' Generate an Excel chart
Dim db As Database
Dim rst As DAO.Recordset
Dim xlapp As Object
[code]...
I've developed and used over the past 20 years to handle my home finances. It includes several charts that were probably created back in Access 2003, which I used up until a couple of weeks ago when I subscribed to Office 365 Home and set up Office 2013, including Access.Most things worked without a hitch. In fact, all my old VBA code seems to run just fine.
However, the charts don't work. The right side of chart gets cut off way short of the margin both in print preview and report view. Everything else looks fine and the data is correct.
I've fiddled and fussed and fangled with zoom, clip and stretch. I've tried to resize the chart area - it just snaps back. I've tried everything I can think of based on 20 years of fussing with Access charts - which has always been a major pain - without success.
I am using this code it is giving me error 3828 at the line marked red. Says cannot reference a table with multi-valued field using an IN clause that refers to another database. Query has fields which gets input from combo box but only one value is saved in it.
Code:
Dim xl As Object ''Excel.Application
Dim wb As Object ''Excel.Workbook
Dim ws As Object ''Excel.Worksheet
Dim ch As Object ''Excel.Chart
Dim myRange As Object
Set xl = CreateObject("Excel.Application")
[Code] ....
I am using an Access 2003 database to create a Word report that contains Graphs. We have since moved to Office 2010, and now the vba code will not select the graph and populate the data, i presume this is because the data is no longer in a datasheet but Excel itself.
View 2 Replies View RelatedI have created a series of pie charts in reports based on queries. The data that is being charted are agree, strongly agree, neither agree nor disagree, disagree, and strongly disagree. I want the colors for these to be consistent in all the pie charts but they are coming out different colors on each chart: i.e., agree may be blue on one pie chart but yellow on another pie chart. How can I change the colors either in the pie charts or legends so that they are consistent on all the pie charts?
View 1 Replies View RelatedHi,
Does anyone know how to export a pivot table to Ms Excel without using the specific button in the Pivot Table View of the form?
Thanks for the help:confused:
Hey all. I posted this over at Mr. Excel too on the Access forums, but I thought maybe someone from this site might know the answer to my question.
I know pivot tables and charts are more up Excel's ally, but my question has more to do with Access I think. I've made a pivot table form from a query and everything is working fine except one thing. I want the table to refresh automatically. I don't want the user to have to go in and refresh the data manually.
I'm thinking I could put something on the forms "On Open" event but I don't know the specific coding. If anyone has any idea on this, I'd greatly appreciate some feedback.
Can anyone help me convert this pivot query to work in sql server please?
I'll love you forever if you help me please :pTRANSFORM Count(Employees.MaritalStatus) AS MaritalStatusCount
SELECT Employees.MaritalStatus
FROM Employees INNER JOIN Offices ON Employees.OfficeId = Offices.officeId
WHERE ((Not (Employees.MaritalStatus) Is Null))
GROUP BY Employees.MaritalStatus
PIVOT Offices.officeLocation;
Can you do a pivot table from a query in Access 2000? I found links that shows new features in Access 2003 that allows it, but no definitive information if it can be done from Access 2000. My tables can pivot fine, but no luck on my queries? If not, is there an easy way to get a query to a table in order to do a pivot table?
View 1 Replies View RelatedI've written this code in Access to create a pivot chart in Excel. All the code listed is good except for the last line. The last line creates another instance of Excel. I can't figure out what the correct syntax is.
Set xlApp = New Excel.Application
xlApp.Visible = True
Set wb1 = xlApp.Workbooks.Open("c:chi estpivot.xlsx"
wb1.Sheets.Add
wb1.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
[code]....
How to incorporate access pivot tables in an access form? Also how to subtotal on changes withinin a field in a form?
View 4 Replies View RelatedHi,
I would like to rename my access file. My problem: I have many pivot in excel link to this database so if i rename it all the links will be down...How can i resolve this?:confused: (of course rebuild all pivots could be a solution but I have around 50 pivots behind my database)
Thanks for your help!
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.
So... I have the pivot table with the following columns...
Sum of Numerator, Sum of Denominator, and a computed field for Rate
My Rate calculation is: iif(Denominator = 0, Null, Numerator/Denominator)...
I have at least 15 of these rates (numerator, denominator, rate) on the same report..it works fine, but my business analyst has clarified the Rate requirement that if the denominator is 0 then the Rate should display 0 (instead of Null)..
When I change my Rate computed field to iif(Denominator = 0, 0, Numerator/Denominator) and press the "Change" button all the numerators and denominators on the pivot table are blanked out... I've refreshed the pivot table and everything; but, I can't stop this from happening. why the heck can't Access' pivot table be as robust as Excel's ???
Access 2010 doesn't seem to like the last line. It gives me an error saying the form name doesn't following access object naming rules.
Sub update_subform()
Dim dbsCurrent As Database
Dim qryD As QueryDef
Dim strSQL1 As String, strSQL2 As String, strSQL3 As String
Dim mytmp As String
Dim proc As String, myot As String
[Code] .....
Is there a way to append a pivot table to a table or possibly make a query based on a pivot table? I need to get a count of Part Numbers and I need the average price for all these parts. Additionally I want to ignore a count of less than 3.
Also I am having trouble filtering on the count in the pivot table... haha, so I was gonna Query on it later on.