I have a form built with multiple buttons. Once the user clicks the button and enters their parameter, I am using VBA to export the data to an excel pivot table. I would like to turn the subtotals to false so as the user clicks the check boxes in the pivot there are not any totals, subtotals, or grand totals. I am not sure how to add that to an existing query?
I have a form built with multiple buttons. Once the user clicks the button and enters their parameter, I am using VBA to export the data to an excel pivot table. I would like to turn the subtotals to false so as the user clicks the check boxes in the pivot there are not any totals, subtotals, or grand totals. I am not sure how to add that to an existing query?
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 ?
I have an Access database with millions of records.I am only interested in a subset of records (250,000+) that I would like to analyze in an Excel pivot-table. My issue is; how do I best export this information to Excel.I've tried filtering and exporting, but all records are exported. I've tried copy and paste but only 65K are allowed. Is there a better way to do this, perhaps linking the Access database?
I have a form, containing a button that when clicked, it runs a query and displays the results in Pivot Table view. The query is set to display only results for the manager name selected on the form. So, when someone opens this database, they first select their name from a drop down box and then click the button to run the query. The query displays average call rate for equipment that person is responsible for. The only problem I am having is that if someone goes back to the main form, selects a different name, and clicks the button again, the query will not refresh. The query won't even refresh when I click the Refresh All button in the Ribbon. In order for the query to update, I have to first click the button to refresh the pivot table and then click the button to refresh the query. Is there a way to do these two steps through visual basic, so that the user doesn't have to do those two extra steps?
Right now, I am using the Docmd.OpenQuery code on my button.
How can I modify the code below so it will display all options (not just AMAZON and ARGOS) ? I don't want to mention all of them in Array because they may change in future.
What Im trying to do is create a query on the back of the one above which will have Problem Source in column 1 and then 2 more columns with their counts in them. So:
SELECT SearchCriteria.[Problem Source], Count(SearchCriteria.[Problem Source]) AS [CountOfProblem Source], Count(SearchCriteria.[Problem Source2]) AS [CountOfProblem Source2] FROM SearchCriteria GROUP BY SearchCriteria.[Problem Source];
I guess this is because it's filtering on the first Problem Source and then looking for non-blanks in the second Problem Source which isnt what Im trying to do!
I have a query I saved which pulls data and a form that creates a pivot table based upon the query.
Is there a way to create a query based upon criteria such as dates to limit my recordset? I'm trying to set date values in a form and update my saved query but I cannot figure it out. Any help would be appreciated.
Here is the SQL I use to create the standard query. What I plan on including through vb is a "Revenue_Date" variable "FROM" and "TO" date in the code to limit the output based upone the dates entered.
SELECT PARENT, TYPE, SUM(TOTAL) AS COMBINED FROM [
SELECT tblStmt_Tracking.Parent_Carrier_Name AS PARENT, '1. IND_Amount' as TYPE, IND_Amount AS TOTAL FROM tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID
UNION
SELECT tblStmt_Tracking.Parent_Carrier_Name AS PARENT, '2. SBG_Amount' as TYPE, SBG_Amount AS TOTAL FROM tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID
UNION
SELECT tblStmt_Tracking.Parent_Carrier_Name AS PARENT, '3. IND_Bonus_Amount' as TYPE, IND_Bonus_Amount AS TOTAL FROM tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID
UNION
SELECT tblStmt_Tracking.Parent_Carrier_Name AS PARENT, '4. SBG_Bonus_Amount' as TYPE, SBG_Bonus_Amount AS TOTAL FROM tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID
UNION
SELECT tblStmt_Tracking.Parent_Carrier_Name AS PARENT, '5. Licensing Fees' as TYPE, Licensing_Fees AS TOTAL FROM tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID
UNION
SELECT tblStmt_Tracking.Parent_Carrier_Name AS PARENT, '6. IND Misc Expenses' as TYPE, IND_Misc_Expenses AS TOTAL FROM tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID
UNION
SELECT tblStmt_Tracking.Parent_Carrier_Name AS PARENT, '7. SBG Misc Expenses' as TYPE, SBG_Misc_Expenses AS TOTAL FROM tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID
UNION
SELECT tblStmt_Tracking.Parent_Carrier_Name AS PARENT, '8. Other Receivables' as TYPE, Other_Receivables AS TOTAL FROM tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID
UNION
SELECT tblStmt_Tracking.Parent_Carrier_Name AS PARENT, '9. Unknown_Amount' as TYPE, Unknown_Amount AS TOTAL FROM tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID ]. AS BREAKOUT GROUP BY PARENT, TYPE ORDER BY PARENT, TYPE;
I have set up a query to filter information between 2 dates using
Between [Start date] And [Finish Date] to filter the info. I'm using office 2003 which allows me to set various pivot charts based on this info with no problems at all. unfortunatly for me several other people are using office 2000 which doesnt have the same chart functionality. so i'm setting up another switch board with 2000 type charts. Right my problem as long as I dont have the Between [Start date] And [Finish Date] codes in my query I've produced the charts I require with no problems. As soon as I add the filter into the query when i go to edit the chart it comes back saying problems updating data. This I presume is because the query wants an input. Is there a way around this?????
I've spent hours searching for this, hopefully one of you can brighten my day!!!!:D
Hi there - I've looked at quite a few posts and although some of them seem to start of heading in the right direction there doesn't appear to be anything that quite hits the spot for what I'm looking for.
I'm trying to create a query that creates the following (see spreadsheet as typing it out didn't help.)
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?
So, I've recently learned that Access 2013 took a step backward and got rid of PivotTables. Unfortunately for me, PivotTables were perfect for summary reports I have to create with large data sets (some with millions of records).
What are my alternatives to PivotTables within Access 2013? I was playing around with the report builder, but it seems to retain all line items and doesn't allow me to collapse everything into a one page report.
I am trying to export text to a 2010 word document from an access 2010 database with DAO. I have successfully been able to export text from the main table tblLandSales via variables (below) and then subsequently a document. I used the following code:
Dim objWord As Word.Application Dim docm As Word.Document Dim rst As New ADODB.Recordset Dim strSQL As String Dim strLandSalesID As String
[Code] ....
Now I want to export other data from a one to many related table where [fk_tblLandSalesID] is the foreign key in the related table and tblLandSalesID is the primary key in the main table.
I need to remove html text from a make table in access 2007. My table name is "Bad Actors Comments Column" and the column where the html text resides is "FirstOfADD_TEXT. VBA code to remove the html text?
I need to export a Pivot Table view to Excel. The issue is with Calculated Total columns which are not exported in excel. Do you know if there is any way I can export the Calculated columns in excel?
I was just wondering if I could use a query and table to create pivot chart? and If so how I could do this? I need information from both in order to create the chart I want.
To keep it short and sweet, my query set up is: Employee, Team Name, and then I have the same field in the column as I do in the data and it presents pass/fail data. I have the data shown as a percent of the row so that there is a success rate shown but I want to remove the grand totals from the rows because it is obviously going to be 100% every time and it's unnecessary.
I'm currently using the following VBA to export all tables within my DB to Excel on separate tabs:
Dim td As DAO.TableDef, db As DAO.Database Dim out_file As String out_file = CurrentProject.Path & "" & "Backup.xls" Set db = CurrentDb() For Each td In db.TableDefs If Left(td.Name, 4) = "MSys" Then 'We do not need MSys tables in excel file Else DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _ td.Name, out_file, True, Replace(td.Name, "dbo_", "") 'We do not need dbo prefix in sheetnames End If Next
But upon exporting I get the following error:
"Run-time error 3274' External table is not in expected format"
I have got an existing query which is exported to excel through DoCmd.Transferspreadsheet. In simple terms the query looks like below:
Group | Client
Group1 Client1 Group1 Client2 Group1 Client3
Group2 Client4 Group2 Client5 Group2 Client6
etc.
As some of the groups contain large number of clients, I am trying to find the code to split groups by pre-specified number of clients and export to excel, as follows:
I a trying to search some product from a search button and two combo boxes text values ,and on serch the vba code is :
Code:
Private Sub Command4_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Dim varRecords As Variant
[code]...
I just dont know,the fuile is created on click of button but with headers only,dtaa is not coming but wheni debug ,in immediate window,data is oming but just not coming in excel file.
I know Access can setup an Outlook Task to auto-export query to Excel, but it requires the Outlook to be always open on the user's computer.
Is there an easy way to setup a schedule that can automatically export a query to Excel and this schedule will then auto-email the exported Excel file to an email address every Monday at 5AM for example?
If this can only be done in VBA, any reference I may start with?
I need to be able to run a number of queries where some of the queries are make-table queries. What I don't want is to have is all the messages that come up during the creation of the table ie deleting existing files and showing the number of records created to place in the file. Is there a way to block these messages?