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 Replies


ADVERTISEMENT

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

How Do I Create A Query Like A Pivot Table?

Nov 15, 2005

Hi Forumers...

I have data in a table that looks like this...

SITE-ID | VALUE
Site 1 | 20
Site 1 | 21
Site 1 | 16
Site 2 | 8
Site 2 | 9
Site 2 | 12
etc...

I would like to create a query that allows me to show summary statistics for each site. eg...

SITE-ID | VALUE_MIN | VALUE_MAX | VALUE_AVG
Site 1 | 16 | 21 | 19
Site 2 | 8 | 12 | 9.667
etc...

Am relatively new to MS Access and can't work out how to create a query that does this. Any help will be appreciated.

Using MS Access 2000 (9.0.7616 SP-3) on Windows 2000

Thanks,

Chris Medlin

View 3 Replies View Related

Pivot Table On A Query In Access 2000?

Oct 11, 2006

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 Related

Summarize A Query / Pivot Table Style

Dec 16, 2013

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.

View 5 Replies View Related

Export Pivot Table Query View In Excel

Jun 26, 2007

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.

View 2 Replies View Related

General :: Using Both Query And Table To Create Pivot Chart

Sep 7, 2014

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.

View 1 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

Modules & VBA :: 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 :: 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

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

Records Not Showing In Pivot Table

Feb 20, 2007

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.

Has anyone had anything similar?

Help would be most appreciated,

Thanks,

Erica

View 1 Replies View Related

Refresh Pivot Table In Form

Sep 21, 2006

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?

View 1 Replies View Related

Pivot Table Datasource Security

Nov 24, 2005

Hi,

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?


Thanks

TS

View 1 Replies View Related

Dynamically Associating A Pivot Table !!

Oct 30, 2006

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

How can I do that??

View 5 Replies View Related

Modules & VBA :: Refresh Pivot Table

Jul 10, 2013

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.

View 2 Replies View Related

Pivot Chart Using Data From More Than One Table

Dec 15, 2012

I need to create a Pivot Chart using the data from more than one table.

The main table has fields including 'Job name', 'task name' and 'hours worked'.

The second table (linked to the first by 'task name') has a field 'allocated hours'.

I would like to plot the 'total time worked' against 'allocated hours' (in a stacked bar style chart), in order to monitor time usage.

View 2 Replies View Related

Export Access Pivot Table To Excel

Jul 30, 2006

Hi,

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:

View 5 Replies View Related

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







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