Thanks Thanks:  0
Likes Likes:  0
Page 3 of 3 FirstFirst 123
Results 21 to 23 of 23

Thread: Can I create a pivot table from two separate worksheets?

  1. #21
    New Member
    Join Date
    Sep 2012
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I create a pivot table from two separate worksheets?

    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.

    Quote Originally Posted by Fazza View Post
    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

    Quote Originally Posted by Fazza View Post
    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.

    Quote Originally Posted by Fazza View Post
    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).

    Quote Originally Posted by Fazza View Post
    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).

    Quote Originally Posted by Fazza View Post
    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.

    Quote Originally Posted by Fazza View Post
    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.

    Quote Originally Posted by Fazza View Post
    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!)

  2. #22
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    8,969
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Can I create a pivot table from two separate worksheets?

    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 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
    If you've posted a clearly explained question & sample data - both input & corresponding output - that can be copied to Excel, THANK YOU.

  3. #23
    New Member
    Join Date
    Apr 2014
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I create a pivot table from two separate worksheets?

    At Scott, did not read this thread thoroughly but this one could help you a bit http://blog.contextures.com/archives...l-excel-files/
    the issue is that the pivot table from power pivot does not group or ungroup the dates column which i am still searching help for this one to overcome it.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •