problem using MS Access 2010. One of my database field's is using multiply values (you can add one than more values in that field by checkbox). So, for example, one record in that ONE field looks like this: "Gastropoda; Mermithidae; Nematoda; Oligochaeta; Scorpiones". When I'm going to "PrivotTable view" I have only "Ga" for instance instead of full names...
View RepliesHi,
For some reason I cant see the drop zones when I am pivot table view. Does anyone know maybe why I cant see this?
When I try to use the 'Add To' box in the Field List Box, the option is grey'd out so I cant click it.
I'm using version 2002.
Can anyone help?
Claudia
so I have this code for exporting all my other queries
Code:
Private Sub Command4_Click()
On Error GoTo Err_cmdTest_Click
'Must 1st set a Reference to the Microsoft Office XX.X Object Library
Dim dlgOpen As FileDialog
Dim strExportPath As String
Const conOBJECT_TO_EXPORT As String = "qryGEM"
[code]...
The codes work perfectly fine but it doesnt work well for pivot table view. What should I add to the add to make it work? I dont want pivot table in datasheet. I did try the codes posted by other people but it works well for if I have only ONE QUERY to export. As you can see, the above codes are for exporting more than one query.
Hello,
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?
Thank you.
I want to "zoom" to the underliyng data from pivot view. When in excel someone doubleclicks a field in a pivot table, it automatically creates a table containing all the lines that field were made up from. I want to achieve the same behaviour in Access.I started to think towards a VBA coda, that could be initiated from the form's double click event. It should go to datasheet view with the prpoer filter criterias.
View 1 Replies View RelatedI have a table called NewTransactions_tbl that I have been using for some time. I am trying to add a new field called CustomerProjectNumber. It's not being displayed when I look at the table view. I've attached an image showing the settings for the field. I am probably missing something obvious.
Any ideas why it is not showing?
I am using Access 2000.
Hi
I am using Access 2002 SP1 and have created a pivot table based on a query which works fine. However if I change the structure of the underlying query by adding another field, the field is not availabe in the Field List in the pivot table. I have used the refresh option but I understand it only applies to the data.
Any ideas?
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 ???
So I have a code that opens a query but I want the PivotChart to show. Below is my code related to opening the query.
If vartyp = 0 Then
varQueryName = DLookup("Query", FileName, Criteria)
If IsNull(varQueryName) = False Then
DoCmd.OpenQuery varQueryName, acNormal
End If
I like to open a report as a pivot chart from my query. How to open a report by making the default view of report as pivot chart?.
I am able to manually create a pivot chart by clicking pivot chart view from the sql view in a report. Then I pull in the required columns to the x axis and y axis and a graph is generated. I do not know how to save it and open the report by default in pivot chart view every time I click a button.
I'm having a big struggle with adding a multi-value lookup field to a table. I need to provide a long pick list of items (from a source table) but these items must not be output (in forms or reports) in alphabetical order, they need to remain in the order that they are in the source table.
Access wants to alphabetize the output even if a I add a number field to the source table and sort by that.
Is there any way I can persuade access to just give the selected items back in the same order as in the source table ?
I am making a new Record in the design view of a table (creating a new field in the table), and I am assigning it a date type, is there a hotkey that will select the field properties sheet so that I can select the format of the field without having to move my mouse?
View 1 Replies View RelatedSo I have an event table with a muti value field with all the people on that event. This people field is look up column from my people table (so shows the name but stores the key).Anyhow what I now need to to do is record logs from the event for each person (from that muti value people field). I want ideally be prompted to enter the log data for each person, and show it related to the event it is for.
View 1 Replies View RelatedI have spent the last couple of days trying to figure out how to make this work.
I have three tables.
tblIntakeMain
[IntakeMainID]
tblIncidentDetails
[IncidentdeatailsID]
tblPersonnel
[PersonnelID]
On the main form I use subforms to link tblIncidentDetails and tblPersonnel to tblIntakeMain. Both subforms can, and do, have many entries. This all works fine. What is not working is the search form I am using.
I am using Gromits most excellent Search Form. The problem is when I create a query, qSearch, to bring together the three tables I get a multiple records which makes the searches very confusing and near useless. Is there anyway around this? Is there something I am missing? Is there another search method I could use that would work in a similar way as Gromits? Please help before the Prozac runs out and I lose my mind--what little it left.
Hi everyone. Apologies if this has come up before, but the search terms I've tried here and on google keep turning up the wrong information.
At work I manage a large database with many tables. It stores data for participants in a research study. Each table stores the data for a different test, so one participant may have multiple records. Primary keys for these tables are defined by a combination of the participant and date of test fields. (Everything is dependent upon a table that stores the static info for participants, so the database is normalized.)
I want to be able to make a table that lists target participants and dates, and then create a query that looks at this table and pulls all the available data from various tables for those individuals that was recorded within one year of the target dates.
I've successfully made queries that meet these criteria while pulling data from only one table. The problem I'm having is that when I try to pull from multiple tables, each with it's own date field that needs to be used as a criterion, I end up excluding almost all the data, because most of the target participants do not have all the requested data within the target dates.
I've tried being inclusive with my criteria (using ORs), but then I end up with tons of data that I don't want and I need to filter through it, which defeats the purpose of the query.
Any advice on handling this issue, or do I basically just need to create a separate query for each table?
I'm sorry if this is too vague, but it's illegal for me to upload any of my own dataset. I could probably come up with an example if it's helpful, though.
Thanks!
I am using MS Access 2007.
I have created a multi-valued field "Product Category" that lookups data items from a linked table. So, the Data Type for the multi-valued field "Product Category" is Number.
Now I want to change the Data Type of "Product Category" from Number to Text, and make a value list that I can type values in and can provide the same data items as the linked table.
How to change item source for the multi-valued field from a linked table to a list that I can type in values? Is there a feature provided by MS Access 2007 can enable such a conversion?
I am using MS Access 2007.
I have created a multi-valued field "Product Category" that lookups data items from a linked table. So, the Data Type for the multi-valued field "Product Category" is Number.
Now I want to change the Data Type of "Product Category" from Number to Text, and make a value list that I can type values in and can provide the same data items as the linked table.
How to change item source for the multi-valued field from a linked table to a list that I can type in values?
Is there a feature provided by MS Access 2007 can enable such a conversion?
I am using access 2007. I click on a table for example. I then click blank report to make a report.
I then insert a text box and then go to control source to select a field from that table. The field doesn't show up when i click view report?
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.
Hi,
Have Access XP on Desk top and lap top. Have about 10 various databases for different projects.
On the desk top, I cannot get a Pivot Table to open in any of the databases, but on the lap top, the same databases all have Pivot tables.
Is it possible that I have turned it off on the desk top?
Thanks.
Mike
Hi everybody.
I have a pivottable query.
This works great.
I have a column with averages.
This works great to.
Now here is my problem:
At the bottom of my Averages column there is a total (sum)
I want an average in stead of a sum.
Please help me guys....
Thank you very very much in advance...
Is there a way that you can manipulate a pivot table, (example adding more item in a list) after the pivot table is created.
View 5 Replies View RelatedIs there a way to get certain parts of the pivot table to not allow the drop down selections?
View 1 Replies View RelatedIve got a query (SearchCriteria) whose data looks like this:
Call ID....Problem Source............Problem Source2
15366....Complaint....................
15224....Collateral Order............Complaint
15734....Delivery.......................
15733....Delivery......................Order Taken
15738....Delivery.......................
15137....Complaint.....................
14238....Other.........................Delivery
15072....Complaint....................Delivery
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:
Problem Source......CountOfProblem Source...........CountOfProblem Source2
Collateral Order................1............................ ..................1
Complaint.......................3................. ..............................1
Delivery..........................3............... ...............................2
Other.............................1............... ...............................0
Ive created a Query with this SQL:
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];
but this gives me:
Problem Source......CountOfProblem Source..........CountOfProblem Source2
Collateral Order.................1........................... ..............1
Complaint.........................3............... ..........................1
Delivery...........................3.............. ...........................1
Other..............................1.............. ...........................1
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!
Any inspiration greatly appreciated as always...
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
many thanks
EQ