Queries :: Use ID As A Pivot

Jun 27, 2013

I work on an enterprise which we send people to do field work, & i am doing a database in Access 2013, there i have a table with all the data of our employees defined by an ID, & i have also a table with all the drivers that take em defined by another ID for them, the problem is when i am trying to make a query..I made a table with the ID of the drivers, the ID of our employees that are on that trip with him, the departure date & the arrival date; and when i try to make my query i want it to display: the both dates, & Name of the driver, his ID, & the truck he is using (all this info is on the driver table), and also to diplay the name, the ID & the rank of the employees going with him (all this info is on the employee table), the problem comes that since i am using the ID to search for every employee info, when i put that more than 1 employee is going in that trip i get this error on the top of table: Expr1000, Expr1001; instead of ID, Name, etc... & no data is displayed.

View Replies


ADVERTISEMENT

Pivot Table Won't Update From Union Queries

Jan 3, 2008

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

Thanks in advance for any assistance.

- Matt

View 5 Replies View Related

How To Call/combine 2 Queries In Pivot Chart

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

Queries :: Import And Convert A Pivot Table?

Aug 11, 2015

I need to import data in this format:The top row of numbers are AttributeIds .In order to import it into my DB I need to convert it into the following fields and records.Any fields that are null should not get a record in the conversion.

View 3 Replies View Related

Queries :: Pivot Table To Show Blank Rows

Mar 4, 2014

I have a PivotTable that I am trying to put together that will give me the following:

Types of Payments - Left
Accounts Where Payments came from - Top
Amount of Payments - Data

I want it to look like a spreadsheet where it will show me all the Types of Payments even though I don't have any data in there. Almost like an Excel Spreadsheet but I want it in Access. I was able to get the Columns to show up even though I had some blanks but now I need the Rows to show up.

View 11 Replies View Related

Queries :: Opening Query In Pivot Chart View

Sep 17, 2014

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

View 1 Replies View Related

Queries :: Copy Data To Excel From Pivot Table

May 21, 2015

I have the following (simplified) normalised data table:

[Account] [SrcUD2] [Amount]
col1 row1 1000
col1 row2 500
col1 row3 500
col1 row4 1000
col1 row5 1000
col1 row6 0
col1 row7 1000
col1 row8 1000
col1 row9 0
col2 row1 100
col2 row2 1000
col2 row3 200
...

I use the query below to pivot the data into the right format for export:

Code:
TRANSFORM First(Amount)
SELECT SrcUD2
FROM source
WHERE LocName="myLoc" AND Entity="LE01" AND PeriodName="QA - 2014" AND ScenarioName="Actual"
AND (Account="col1" Or Account="col2" Or Account="col3" Or Account="col4" Or Account="col5" Or Account="col6" Or Account="col7")
AND (SrcUD2="row1" Or SrcUD2="row2" Or SrcUD2="row3" Or SrcUD2="row4" Or SrcUD2="row5" Or SrcUD2="row6" Or SrcUD2="row7" Or SrcUD2="row8" Or SrcUD2="row9")
GROUP BY SrcUD2
PIVOT Account

which yields the following table as the query result:

SrcUD1 col1 col2 col3 col4 col5 col6 col7
row1
row2
row3
row4
...

which is great except that I want to transfer the results to Excel using the CopyFormRecordset method without the metadata of column 1 (row1, row2 etc.

View 1 Replies View Related

Queries :: Pivot Charts - Displaying Values As Percentage

Apr 8, 2014

I 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.

View 7 Replies View Related

Queries :: Exporting Query To Pivot Table Remove Subtotals?

Nov 19, 2013

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?

View 1 Replies View Related

Queries :: Removing Grand Totals From Query Pivot Table

Jun 17, 2013

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.

View 13 Replies View Related

Tables :: DateTime - Running Pivot Queries To Excel To Do Analysis Of Data

Aug 27, 2013

I have a larget transaction data set in access with Datetime column/filed.

I have been running pivot queries to excel to do analysis of the data but the datetime field is returning too many unique values for the pivot table to run.

What is the best way to reduce the datatime field to date only and where should this be done?

i.e. should I have a calculated field that trims datetime or should I set someohting up in Powerpivot?

View 7 Replies View Related

Queries :: Append Data In Access Pivot Table To A Table (or Requery On It)

Jan 22, 2015

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.

View 2 Replies View Related

Pivot Table

Oct 13, 2005

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

View 3 Replies View Related

Pivot Table

Jan 6, 2006

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...

View 3 Replies View Related

Pivot-like Problem

May 4, 2005

Hello,

I am a student on a apprenticeship about MS access. My assignment is to build a MS Access application. But i am stuck. I will put my problem as simple as possible.

I have 2 tables. 1 with employees:
Department(string) | EmployeeNr(AutoNr) | EmployeeName(String)


And one with a couple of times:
Time(string)


The table i need is as follows:
Department(String) | EmployeeName(String) | EmployeeNr(AutoNr) | Time1(Boolean) | Time2 (Boolean) | time3(Boolean) | ......

Unfortunatly i have absolutely no idea how to do this and it is killing me :S

Can anybody help me? Thanks in advance.
The CitiZen

View 5 Replies View Related

Manipulate A Pivot Table

Apr 4, 2007

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 Related

Pivot Table Question

Mar 16, 2007

Is there a way to get certain parts of the pivot table to not allow the drop down selections?

View 1 Replies View Related

Pivot Tables : Total

Apr 3, 2007

Hi!

I cannot stop Access 2002 from automatic creation of Total column for every column. Is there any method to compute totals only for some columns?

I would appreciate any help!

With kind regards
Maxim Ivashkov

View 1 Replies View Related

Query To Act Like A Pivot Table???

Sep 2, 2005

Ive 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...

View 2 Replies View Related

Pivot Table Query

Jul 7, 2006

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;

View 3 Replies View Related

Pivot Table + Query

Oct 16, 2007

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

View 2 Replies View Related

Getting Pivot Charts Out Of Access

Nov 24, 2004

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!

View 3 Replies View Related

Week No's On Pivot Charts

Feb 1, 2005

CAN 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 Related

Pivot Table - Where To Begin

Aug 2, 2006

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.

View 3 Replies View Related

Pivot Table Problem!

Nov 17, 2006

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.

View 1 Replies View Related

Forms :: Pivot Chart Going Over 100%?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved