Can I create a pivot table from two separate worksheets?

scottbass

New Member
Joined
Sep 3, 2012
Messages
46
This is the final query I want to execute:


Code:
SELECT
a.caseid As CaseId,
a.analyst As Analyst,
a.lastname As LastName,
a.firstname As FirstName,
a.totalamt As Total,
b.applicamt As Applicable,
c.agreedamt As Agreed,
(b.applicamt - c.agreedamt) As WriteOff,
d.paidamt As Paid,
(c.agreedamt = d.paidamt) As Outstanding
FROM
(
(
(SELECT caseid, analyst, lastname, firstname, SUM(BenefitPaid) As totalamt FROM [Claims$] GROUP BY caseid, analyst, lastname, firstname) a
LEFT JOIN
(SELECT caseid, SUM(BenefitPaid) As applicamt FROM [Claims$] WHERE Applicable='Y' GROUP BY caseid) b
ON
a.caseid=b.caseid
)
LEFT JOIN
(SELECT caseid, SUM(AgreedAmount) As agreedamt FROM [Money$] GROUP BY caseid) c
ON
a.caseid=c.caseid
)
LEFT JOIN
(SELECT caseid, SUM(Total) As paidamt FROM [Payments$] GROUP BY caseid) d
ON
a.caseid=d.caseid

This was working for me once, but I breathed on it wrong and am now getting the error "Could not add the table "(" ". Well, thanks MS for the helpful error message.


THIS IS VALID SQL! Perhaps there is a data issue on this new workbook (I cut and pasted my real data to this test workbook), but the error message sucks.


Re: your last post...I'm now trying to create the queries "manually" via the query wizard (ALT-D-D-N), then use VBA to refresh. This will require much less VBA than the ADO/recordset approach.

OK, payments are entered directly in the spreadsheet. I had expected like the Claims data it would come from another application/database.
Nope, cheques in the mail :)

So doing the two steps so that the AgreedAmount is simply retained without the mucking around required with an alternative approach. I still am unsure if you're clear on that approach?
Nope, not clear, although essentially I think it means breaking up the query into multiple parts. While breaking up the query into two parts may be the way to go, my 3 table (4 alias) outer join is valid SQL and should work. However, given the lack of standard SQL support in MS query, perhaps that is the best approach. It appears that anything past two LEFT JOINS is "cumbersome", requiring parentheses not needed in ANSI-compliant SQL.

Though I see you have the same result as you describe Money sheet refreshes have blanks in the AgreedAmount for new records and other values stay there since 'the Money worksheet itself forms part of the query'. It sounds like you're doing the refresh a little like I'm thinking except with a recordset to re-generate the entire Money data each time, hence you query Money when making that recordset. Whereas I am keeping the exisiting Money data and using the extra scratch table to retain the pre-existing AgreedAmount values.
Nope...I Googled "Excel Query Table", and got that hit from Microsoft with the ADO/recordset approach (not a query table). I'm now trying to get the query wizard to embed a query table (or two using your approach) onto a scratch worksheet, then refresh Money. Ideally, Money would be the table output from the second embedded query table, rather than copying cells from the scratch workbook (this is different from what I previously stated).

1) Using the query wizard you first create a data source. I've never done that, and I've been doing these queries for ~10 years. ALT-D-D-N and then Excel (or whatever) files & follow wizard.
When I say "query wizard" that's what I mean...ALT-D-D-N, first define a data source, then define the query. For my query, I pick any table (worksheet), edit the query manually, click the SQL icon, then paste in my query. And it appears that I could create many queries from the single data source (which is pointing to my workbook).

2) I just make each query from scratch, and like I just wrote, I don't use data sources. Here are three basic ways to do these things: one, create them manually (no VBA) once, refresh manually as required; two, create them manually & use VBA to refresh (probably just the connection once you ensure there are no hard coded file paths in the SQL. Easy enough too to just put the SQL in each time); three, create them via VBA (to be sure they are what you are expecting, & not changed by users) & refresh using VBA.
Sorry for being dense...using ALT-D-D-N, the first thing you do is create the data source ???!!! But perhaps when you say "...I don't use data sources", you mean you override those settings via VBA? And yes, I'm looking to do #2: create them manually and use VBA to refresh.

Note the code I gave last time to debug.print the .connection and .sql is for Excel 2003 & earlier. For later versions the query table object reference has changed.
Yep, Googling now. I have to use a list object...This bit I should be able to get via Google.

A beer'd be nice. Thanks, Scott. If you're over in Perth I can shout you a couple
I spent about a year in Perth on a consulting gig (not Excel lol). Lived in Subiaco near Lake Monger. And I'll do the shouting (at least the first three!)
(y)
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,331
In the SQL near the end of the select, is : (c.agreedamt = d.paidamt) as Outstanding

You may want that. Or you may want : (c.agreedamt - d.paidamt) as Outstanding

This step about defining a data source. Well yes I do use a data source but not the ones that you define & save via a long-winded process. Just select Excel files and continue.

When you have some time, these may be of interest

Querying External Data in Excel
which leads to Querying External Data in Excel

Daily Dose of Excel » Blog Archive » Create a QueryTable from an Excel Source in VBA

The image at the bottom of page 2 selects data sources - this is the screen I never seem to see.
http://fleet-maintenance.com/public_downloads/EXCEL_DATA_FROM_SQLSERVER.pdf

maybe it is just because I select Excel files at an earlier choice, not <new data="" source=""> like you see (not used) at
Daily Dose of Excel » Blog Archive » Parameters in Excel external data queries

this looks a really long way to get there - not what I do
Import External Data</new>
 

Watch MrExcel Video

Forum statistics

Threads
1,096,272
Messages
5,449,378
Members
405,564
Latest member
Daisygal

This Week's Hot Topics

Top