Exel vs. Access- large amount of data, need pivot table

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

 

[dhookom]

It's hard to tell a lot without seeing your SQL view. Specifically, if you didn't use "UNION ALL" rather than "UNION", you may experience a huge performance hit.

I prefer to leave the data in Access or other database and link to the records from Excel.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MrsMope]

dhookom,
Are you telling me to use union all? My current union query does use the union all statement.

Thanks,

 

[dhookom]

I was making a WAG about UNION ALL since you provided very few specifications about your query and data.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MrsMope]

dhookom,
I must be dumb this morning becuase I still don't get it.
Anyway here is my sql code:

Source query

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

Union:

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

 

[dhookom]

My only suggestion would be if you have 100,000s of records in each table, consider moving to SQL Server and creating your union query there. I assume you have indexes on all your join and group by fields?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MrsMope]

Duane,
I like your idea to move to SQL but, I don't have that authority to write a query on a SQL Server, or to request that one be built for me to run this on. Also, I cannot tell if there is indexes on the fields.

I am going to have to have the end user drill down a little bit before I give the data, thanks for your suggestions.