Large amount of data, need pivot table 1

[MrsMope]

Hello,
I have 4 companies, each one's data is stored on a different database. I need to pull all of them together onto an excel sheet for a pivot table.
I have created a union query between the 4 companies, however this is very very slow, and it is in MS Access, the end user for this is not well versed in access and would prefer to do it all in excel.
Even after putting criteria into the union query, speeds are less than desirable ( over 5 minutes)

Questions:
What is the best way to handle a large amount of varied data from several sources?
I thought a union query would do the trick, maybe not.

How can I utilize excel to pull this data in?

What is the best way to pass parameters to Microsoft Query to fill a pivot table?

Is this the best solution, or is there a way to open an excel sheet in MS Access with the data?

 

[SkipVought]

Hi,

As you are experiencing, similar data chopped up into different tables is a TERRIBLE design mistake!

As long as the sum of the counts of the rows from each table does not exceed 65,000, I'd do a UNION of all using MS Query in Excel and THEN use THAT result as the PivotTable Source.

You can query using parameters. I almost always macro record EDITING the qt after I ADD it. The add my Parameters in the SQL code that I recorded, like...

Sub GetOPER()
    Dim sConn As String, sSQL As String
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=D:\My Documents\Ed\alanizdata for 901-044-and 901-045 partsNEW.xls;"
    sConn = sConn & "DefaultDir=D:\My Documents\Ed;"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
    
    sSQL = "SELECT Distinct A.OPER "
    sSQL = sSQL & "FROM `D:\My Documents\Ed\alanizdata for 901-044-and 901-045 partsNEW`.`alanizspandata$` A "
    sSQL = sSQL & "WHERE (A.`C/C`='[b][red]" & [SelectedCC] & "[/red][/b]') "
    
    With wsOperList.QueryTables(1)
        .Connection = sConn
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With
End Sub

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[KenWright]

Do you have more than 65K rows, as if not then you can dump the lot into Excel in a single sheet and Pivot from there.

Failing that you could always create a single table with the data in Access and then just create a Pivot table normally in Excel but point the source to the mdb file using 'Get External data'. User will probably be comfortable with either of those options, and each allows them full normal Pivot functionality.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[MrsMope]

Thank you to both for replying,
I will give a little more information
I have four queries ( one in each company/server) that I pull the infomration I need from 3 different tables, then I have these tables 'connected' via a union query ( using union all). Three of the companies are fairly small and eventhough they take a little time, not to bad. The main company is SLOW, this table has way over 65000 lines in it. So I created a form with a data parameter on it, my 4 source queries are based on that date range to at least narrow it down and then I have a docmd.transferspreadsheet method to transfer it to excel. I would prefer my user did not have to use two separate tools to get the data ( Access and Excel) and when I use the transferspreadsheet method, the data that was in the spreadsheet isn't cleared before new data is imported, so I may have 'leftovers' that I don't want. IE: 1st query pulls data from 2005. User runs program again, chooses data from 10/04- 12/04, obviously this returns less rows, when imported I still have 05 data because the sheet wasn't cleared prior to import.

any suggestions?

 

[SkipVought]

I'd perform the queries directly from Excel. All the problems you have raised are NOT problems, if you run from Excel. The QueryTable is populated with new refreshed data with each execute.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[MrsMope]

Skip,
I like that idea, here is one of my four source queries

SELECT WaubeRM00101.CUSTNMBR, WaubeRM00101.CUSTNAME, WaubeWB_CPICH.SLPRSNID, WaubeRM00301.SPRSNSLN, Sum(WaubeWB_CPICH.WB_COMM_CALC) AS SumOfWB_COMM_CALC, WaubeWB_CPICH.WB_DATE_COMM_PROC, WaubeWB_CPICH.WB_NEWRENEW, WaubeWB_CPICH.WB_COMM_ID, 1 AS Company, WaubeWB_CPICH.WB_TIER_PERC
FROM (WaubeWB_CPICH LEFT JOIN WaubeRM00101 ON WaubeWB_CPICH.CUSTNMBR = WaubeRM00101.CUSTNMBR) LEFT JOIN WaubeRM00301 ON WaubeWB_CPICH.SLPRSNID = WaubeRM00301.SLPRSNID
GROUP BY WaubeRM00101.CUSTNMBR, WaubeRM00101.CUSTNAME, WaubeWB_CPICH.SLPRSNID, WaubeRM00301.SPRSNSLN, WaubeWB_CPICH.WB_DATE_COMM_PROC, WaubeWB_CPICH.WB_NEWRENEW, WaubeWB_CPICH.WB_COMM_ID, 1, WaubeWB_CPICH.WB_TIER_PERC, WaubeWB_CPICH.WB_CASH_COLL
ORDER BY WaubeRM00101.CUSTNMBR, WaubeWB_CPICH.SLPRSNID, WaubeWB_CPICH.WB_DATE_COMM_PROC, WaubeWB_CPICH.WB_NEWRENEW, WaubeWB_CPICH.WB_COMM_ID;

And then my union is very simple

SELECT * FROM qryWaubeSalesSummary
union all
SELECT * FROM qryHartSalesSummary
union all
SELECT * from  qryMGUSalesSummary
UNION ALL SELECT * FROM qryNBRSalesSummary;

And you are telling me I can do the above in MS Excel instead of access? I tried to set up a query in Excel, but when I attempted to put a parameter on the salesperson ID the application hung for about 10 minuts, then I closed it. Like I said I think it is the amount of data.

 

[SkipVought]

[tt]
WaubeWB_CPICH.WB_CASH_COLL
[/tt]
is in GROUP BY by not in Select ???

Leave off the order by clause -- rather SORT in Excel after resultset returned. In Data Range Properties, check the box for perserve sort/format etc.

Your example does not show a criteria for WaubeWB_CPICH.SLPRSNID????

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[MrsMope]

Hey Skip,
Yes you are correct, the SLPSNID is not listed as a parameter becuase maybe the user wants to pull by salesperson, maybe not. Maybe he wants to pull by company or by tier or a multitude of different options. I guess that is where I am stuck because I don't know the best way to have the end user narrow down the search for the queries.

 

[SkipVought]

for example...

Sub test(sWhichOne As String, vValue)
    Dim sSQL As String
    
    sSQL = "SELECT "
    sSQL = sSQL & "  WaubeRM00101.CUSTNMBR"
    sSQL = sSQL & ", WaubeRM00101.CUSTNAME"
    sSQL = sSQL & ", WaubeWB_CPICH.SLPRSNID"
    sSQL = sSQL & ", WaubeRM00301.SPRSNSLN"
    sSQL = sSQL & ", Sum(WaubeWB_CPICH.WB_COMM_CALC) AS SumOfWB_COMM_CALC"
    sSQL = sSQL & ", WaubeWB_CPICH.WB_DATE_COMM_PROC"
    sSQL = sSQL & ", WaubeWB_CPICH.WB_NEWRENEW"
    sSQL = sSQL & ", WaubeWB_CPICH.WB_COMM_ID"
    sSQL = sSQL & ", 1 AS Company"
    sSQL = sSQL & ", WaubeWB_CPICH.WB_TIER_PERC "
    
    sSQL = sSQL & "FROM (WaubeWB_CPICH"
    sSQL = sSQL & "  LEFT JOIN WaubeRM00101"
    sSQL = sSQL & "    ON WaubeWB_CPICH.CUSTNMBR = WaubeRM00101.CUSTNMBR)"
    sSQL = sSQL & "  LEFT JOIN WaubeRM00301"
    sSQL = sSQL & "    ON WaubeWB_CPICH.SLPRSNID = WaubeRM00301.SLPRSNID "
    
    sSQL = sSQL & "Where "
    Select Case sWhichOne
        Case "Salesperson"
            sSQL = sSQL & "WaubeWB_CPICH.SLPRSNID='" & vValue & "' "
        Case "CustomerName"
            sSQL = sSQL & "WaubeRM00101.CUSTNAME='" & vValue & "' "
    End Select
    
    sSQL = sSQL & "Group BY"
    sSQL = sSQL & "  WaubeRM00101.CUSTNMBR"
    sSQL = sSQL & ", WaubeRM00101.CUSTNAME"
    sSQL = sSQL & ", WaubeWB_CPICH.SLPRSNID"
    sSQL = sSQL & ", WaubeRM00301.SPRSNSLN"
    sSQL = sSQL & ", WaubeWB_CPICH.WB_DATE_COMM_PROC"
    sSQL = sSQL & ", WaubeWB_CPICH.WB_NEWRENEW"
    sSQL = sSQL & ", WaubeWB_CPICH.WB_COMM_ID"
    sSQL = sSQL & ", 1"
    sSQL = sSQL & ", WaubeWB_CPICH.WB_TIER_PERC"
    sSQL = sSQL & ", WaubeWB_CPICH.WB_CASH_COLL"
End Sub

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}