multiple queries in Excel

[Guest]

I have successfully created a query to pull data into an excel spreadsheet
from SQL. The problem is that I have about 10 select count(*) statements that
pull pretty much from the same tables with different criteria. Once I've
create the first query, I can't seem to figure out how to create another one
as the create new query is greyed out. In looking at the script that gets
created from using the wizard I can't seem to figure out how to duplicate it
and where the value will fill in. Can anyone shed some insight on the easiest
way to fill cells in based on data from one cell, but bring the data back
into multiple cells using multiple queries? Thanks Rick

 

[Guest]

There should be no problem having multiple queries in a workbook, even from
the same source (except Excel/MSQuery seems to hold a separate connection
open for each query until the book is closed - bummer!). But keeping in mind
that the querytable needs to be able to insert and delete rows for each query
(when they are updated) there can be problems putting them on the same sheet
(if the column ranges overlap, then one query refreshing will potentially
delete data from the other query). The best solution is to have each query
on a separate worksheet and another master sheet to summarize and tally the
results. Or, if you need the data all together (and if the result sets
contain the exact same fields, both name and number) then you might want to
consider a UNION query in SQL as a way of combining the results into one
querytable.