Multiple make table queries in one?

  • Thread starter Thread starter Beth Eadie
  • Start date Start date
B

Beth Eadie

I have a single table in my database containing school names, usernames, and
password for user accounts. I am trying to find an easy way of exporting out
the data into an Excel spreadsheet, using 1 tab for each school.

Now, I have the code for a make table query which works perfectly. Example
code:

SELECT [School Name], [Student ID], [Password]
INTO [Excel 8.0;Database=C:\StudentIDs.xls].EDS
FROM [DPS STUDENT]
WHERE [School Name]="Edison";

SELECT [School Name], [Student ID], [Password]
INTO [Excel 8.0;Database=C:\StudentIDs.xls].WOW
FROM [DPS STUDENT]
WHERE [School Name]="World of Wonder";

My question is....is there a way to combine this code together in one single
query so that I don't have 20+ queries in this database (we have 20+ schools
in the district). I know I can create the queries and have them all run from
a macro, but I would like to know if there is a simpler way of doing this.

Any help is greatly appreciated.

Thanks,
Beth
 
I have a single table in my database containing school names, usernames, and
password for user accounts.  I am trying to find an easy way of exporting out
the data into an Excel spreadsheet, using 1 tab for each school.

Now, I have the code for a make table query which works perfectly.  Example
code:

SELECT [School Name], [Student ID], [Password]
INTO [Excel 8.0;Database=C:\StudentIDs.xls].EDS
FROM [DPS STUDENT]
WHERE [School Name]="Edison";

SELECT [School Name], [Student ID], [Password]
INTO [Excel 8.0;Database=C:\StudentIDs.xls].WOW
FROM [DPS STUDENT]
WHERE [School Name]="World of Wonder";

My question is....is there a way to combine this code together in one single
query so that I don't have 20+ queries in this database (we have 20+ schools
in the district).  I know I can create the queries and have them all run from
a macro, but I would like to know if there is a simpler way of doing this..

Any help is greatly appreciated.

Thanks,
Beth
Click to expand...

This can be done with a little VBA-code, provided there is a table
listing school names and codes (WOW, EDS).

Groeten,

Peter
http://access.xps350.com
 
Actually, having 20 saved queries and exporting them one after another in a
macro (or VBA code) is about the easiest way to do in, in my opinion.
However, instead of make-table queries, I would create regular Select
queries,

SELECT [School Name], [Student ID], [Password]
FROM [DPS STUDENT]
WHERE [School Name]="Edison";

SELECT [School Name], [Student ID], [Password]
FROM [DPS STUDENT]
WHERE [School Name]="World of Wonder";

and then use the TransferSpreadsheet macro action (or VBA Method) to export
them individually to a spreadsheet. If you give the same workbook name for
each action, all of the queries will be exported to the same workbook on
different tabs.


--
--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 could write a vba routine to loop through a distinct recordset of school
names (assuming that you are storing both School name and the abbreviation) or
all willing to use the full school name as your sheet title.

It will still be multiple queries - one for each school. You will just be
using VBA to build the queries on the fly.

The code might look something like the following.

Dim StrSQL as String
DIM dbAny as DAO.Database
DIM rstAny as DAO.Recordset

strSQL = "SELECT DISTINCT [School Name] as School,
"Replace([School Name],"" "","""") as SheetName FROM [DPS Student]"
Set dbAny = currentdb()
Set rstAny =dbAny.CreateRecordset(strSQL)

With rstAny
WHILE not .eof
strSQL = "SELECT [School Name], [Student ID], [Password]" & _
" INTO [Excel 8.0;Database=C:\StudentIDs.xls]." & rstAny!SheetName & _
" FROM [DPS STUDENT]" & _
" WHERE [School Name]=""" & rstAny!School & """"
dbAny.Execute strSQL, dbFailonError

.MoveNext
WEND
End with


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I have a single table in my database containing school names, usernames, and
password for user accounts. I am trying to find an easy way of exporting out
the data into an Excel spreadsheet, using 1 tab for each school.

Now, I have the code for a make table query which works perfectly. Example
code:

SELECT [School Name], [Student ID], [Password]
INTO [Excel 8.0;Database=C:\StudentIDs.xls].EDS
FROM [DPS STUDENT]
WHERE [School Name]="Edison";

SELECT [School Name], [Student ID], [Password]
INTO [Excel 8.0;Database=C:\StudentIDs.xls].WOW
FROM [DPS STUDENT]
WHERE [School Name]="World of Wonder";

My question is....is there a way to combine this code together in one single
query so that I don't have 20+ queries in this database (we have 20+ schools
in the district). I know I can create the queries and have them all run from
a macro, but I would like to know if there is a simpler way of doing this.

Any help is greatly appreciated.

Thanks,
Beth
Click to expand...

This can be done with a little VBA-code, provided there is a table
listing school names and codes (WOW, EDS).

Groeten,

Peter
http://access.xps350.com
Click to expand...
 
Back
Top