Using Microsoft Query for many SQL queries.

[Mr. Snrub]

I am a novice to Microsoft Query and I am trying to find the best approach for creating an Excel workbook that will display results from SQL queries. My workbook will display the results of nine SQL queries for each year this report covers, and the data goes all the back to 1999, so that's 14 years of data and 126 queries total. These queries are all going to be of the form "SELECT sum(amount) FROM ..." and then at the bottom of the report I want to have a grand total summing up the amounts returned by the queries. I have several questions:

1. It seems like for every "select sum(amount)" query I create with Microsoft Query it displays the value in the cell, which is what I want, but it adds another cell above that "sum(TABLENAME_COLUMNNAME)..." I don't want this in my worksheet, especially since I want to place the results of the nine queries in a 3x3 cell layout. How do I remove this?

2. Because there's going to be 126 query results, does this mean I have to have 126 separate data connections? For every single one of the 126 queries, do I really have to: 1. Click on the cell where I want the query results, 2. Click on Data -> From Other Sources -> From Microsoft Query, 3. Click on the ODBC data source name, 4. Click on the table name, 5. Click on the SQL icon in Microsoft Query, 6. Enter in the SQL statement, and 7. Click the "Return Data" icon? That's a lot of steps to do 126 times. Is there a better approach to this? The tables are only ~100,000 records big, and there are only two of them; should I import the entire tables in a couple worksheets?

 

[RoryA]

Why do you need separate queries for each year? Can you not provide the totals for each of the 9 queries (if they need to be separate) grouped by year so you only use 9 queries? Then you can link to those results elsewhere if you need a different layout. A separate query for 1 result seems excessive.

 

[Mr. Snrub]

well i guess i could just write a big query composed of 14 union blocks. would i really get a performance boost though?

 

[RoryA]

well i guess i could just write a big query composed of 14 union blocks. would i really get a performance boost though?

No way I could say without knowing your database or queries.

 

[Fazza]

it sounds like a cross-tab result might do what you want - perhaps just a single query

if it has to join data from two sources, it might be like

TRANSFORM SUM(amount)
SELECT row fields
FROM (SELECT *
FROM table_1
UNION ALL
SELECT *
FROM table_2)
WHERE criteria
GROUP BY row fields
HAVING criteria
PIVOT column_field

Or a simple GROUP BY year like Rory mentioned

In a situation where 126 queries were required, it can be automated via VBA. If going that route, instead of query tables you might prefer ADO. With ADO one can open a connection and execute multiple queries efficiently.

HTH