Help me! I'm creating a supplier database at the moment. I have a pivot table that allows users to filter the list of suppliers by programme, primary product & company name. I'd like to make it so that double clicking on a company name opens that suppliers details in a new form (which I have already made). How do I do this?
While the knowledge is flowing, I'd also like to make a button that will reset/remove the filtering from the pivot table. Any idea's?
I have a sub query that was working until a couple of days ago when it decided to stop.
I have a Table called TblFieldValues which whenever a new value is entered into my relational Db. Each new Value is given a ValueID and is Dated (full date/time stamp)
My Sub Query ensures that for each FieldID (ie if more than 1) it selects/shows the top most Date....But it is not grabbing all the fields for some reason???
Here is the SQL limiting criteria to a bare minimum (QuoteID) SELECT Main.FieldID, Main.QuoteID, Main.QuoteTypeID, Main.SubSection, Main.FieldValue, Main.NumberFieldValue, Main.CalcFieldValue, Main.Date, Main.ValueID FROM TblFieldValue AS Main WHERE (((Main.QuoteID)=[Forms]![FrmQuote]![QuoteID]) AND ((Main.ValueID) In (SELECT TOP 1 Sub.ValueID FROM TblFieldValue AS Sub WHERE Sub.FieldID=Main.FieldID ORDER BY Sub.Date DESC)));
I had a thought it might be the way the info is put into TblFieldValues, as it is often put in via Code, in fact only when it is put via code is it not showing up as a rule. So I had a look at the code that inserts it.
I had Now() in replace of strDate, but tried changing to strDate and diming strDate as Date and then setting strDate = Now() but doesn't really change it in the table.
I am certain it is in this somehow? Any ideas???? Your help will be greatly appreciated...
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.
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.
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).
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.
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
So I have a layout formulated in my head on how I want this to look but I'm really unsure of what all I need to setup to make this happen. I've never been fond of pivot tables and due to this don't utilize them much to know how to appropriately use it in this instance.
The attached pic file shows how I'd like the form to be setup. There will be fluff at the top that basically signifies what the record is (i.e. name of the jobsite). (A) is a drop down that will list a bunch of categories (bathroom, kitchen, laundry, etc.) Upon selecting that it will populate (C) for the subcategories relative to the main category (A). (C) is a list of part names that are associated with (A). The Column heading (B) is the Plan number which there can be 3 different plans or 5 different plans. The inside data (D) is the count of each part (C) used in each Plan (B) (i.e. 2 sinks in Plan 1, 3 sinks in plan 2, etc). Listed right below that number I want to link a picture (E) showing what that, for example, sink looks like.
So I'm not sure which tables need to be created to make this Pivot Table work. I know it's not an easy solution but if someone could take a little time to help me figure it out I'd appreciate it.
I have a pivot table which I want to associate with a table in my access database. The problem is that when I try to associate it at design time, it gives me an error saying that the database is locked and I cannot access it.
I am using access 2003. Is there any solution to this problem....like making the database unlocked. If not, I wil have to create another database with the same table which seems crazy to me.
Hi, I hope someone can help me. I have a database as thus:
Several tables ->appended together using 'union select' into a query called 'sheet1'-> information that is coded converted via linked tables in a query called 'sheet2'
'Sheet 2' looks completely fine - it works dandy but when I try and run a pivot table not all of the values in one column that should show don't even come up as an option.
The values that are missing on the pivot report do actually exist in the query that it is running from.
There are no filters on and Pivot tables work okay on the origional tables.
I'm trying to use a PivotTable Form (in Access), in order to automate a process (previously data was exported to Excel and the pivot table was created manually). I've got my pivot table form working fine but I need to provide the user with a way to refresh the Pivot table - i.e. re-run the underlying query.
In Access Help it says to select design view and then click the Refresh button (the one with the big red '!' icon). This does work and the data is updated but it's not a user friendly option.
Opening the form doesn't automatically refresh the pivot either, so does anyone know a way I can provide a button with VBA code or whatever to manually update the pivot so it reflects changes in the underlying data?
Not sure what section this should go in but it vaguley relates to access and VBA so it's in here.
After a complete nightmare trying to do some decent graphs in ASP i decided to try using excel linked to my Access DB.
I've set up my pivot tables in Excel to link to the Db on the webserver. The user can open the workbook from the website and view the reports and graphs.
The problem is the database contains sensitive data for more than one organisation.
I have a workbook for each organisation selected dynamically when the user logs in to the webpage.
I've set a parameters in the pivot table querys so that all querys will be filtered using the users organisation code.
Trouble is you can access the data source of the pivot table through the wizard and ammend the parameter to view other organisations data.
Is there a way of preventing the user from accessing this feature i.e locking the wizard?
I have a pivot table, It is associated with a table in an another database (access database ofcourse!). What I am doing is that I am inserting new data on a click of a button in that table but the pivot table shows the same old data, it does not refresh..!!
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.
I searched the internet and this forum for the answer to this. I did find the exact question posted in this forum way back in 2002 but there was no reponse...
When updating an Excel Pivot table using an Access Union Query as it's source, I receive the message "[Microsoft][ODBC Microsoft Access Driver] Too Few Parameters".
I found a rather elegant solution here (http://groups.google.co.uk/group/microsoft.public.excel.querydao/browse_thread/thread/1ca76034adc10c1a/204261bda38c118c) Unfortunately, this appears only to work for Access 2003. Does anyone have a solution for Access 2000?
Perhaps I should insist that our IT department upgrade. :p
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.
