Exporting Multiple Queries to Excel

  • Thread starter Thread starter rollover99 via AccessMonster.com
  • Start date Start date
R

rollover99 via AccessMonster.com

I have a DB that I want to send multiple queries to Excel. I tried to do
this from excel but I kept getting an error that said something to the effect
of not enough parameters expected 1.

Here is the SQL from the query:

SELECT [Issue Frequency base].[Process Desc], Sum([Issue Frequency base].
SumOfFreq) AS Frequency
FROM [Issue Frequency base]
GROUP BY [Issue Frequency base].[Process Desc], [Issue Frequency base].Year
HAVING ((([Issue Frequency base].Year)=Year(Date())));

In the Issue Frequency base query their is field that requires input from a
form combo box before the report can run or for that matter anything else in
the DB.

What I either want to do is open the excel spreadsheet and have it
autopopulate the data from the 4 qureies or download the data from the
queries having it delete the old data and placing it in one sheet at
different places. Downloading the data would be best because the sheet
called 'Data' has all the information for multiple graphs. The same graphs
are used by multiple departments and that is why the initial form in access
that loads asks for the dept they are attached to.

Can someone help???
 
Am I understanding there is a worksheet in your Excel file named Data and
that you have 4 Access queries that all need to put data in the Data
worksheet at different locations and that the data exported to Excel needs to
be filtered by department?

I just want to be clear on that, because there are a number of ways to do
this depending on exactly what you are doing.
 
On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ExportToExcel.mdb" which uses a different approach that you
might want to investigate.

As for the Parameters thing, I'd programmatically re-create the query with
the actual values from the combo box rather than referencing it directly. I
find this to be more robust approach and works quite well with the Export
method above.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
You are correct in that I have an excel spreadsheet that has a sheet called
data within it. When a user selects a dept in the opening form it is used in
my queries. Here is an example of part of the SQL:

WHERE ((([QCPC Master].Status)<>"closed") AND ((Departments_1.Department)=
[Forms]![Home Dept]![Combo0]))

This where statement is the so called filter/criteria.
 
Back
Top