Is there a simple way to create multiple output files from a single query?

  • Thread starter Thread starter Gary J. Dikkema
  • Start date Start date
G

Gary J. Dikkema

I need the ability to build up to 58 output files from a single query.
There's a field that can be used to control this.

Tough question?
 
Maybe it's an Excel macro I'm thinking of that allows subsetting into
different work sheets?

Anyways I have a control field with 57 values and I want to build a
worksheet or unique tables based on that.

Actually I want to be able to take the worksheet and attach each to a unique
email account for mailing.
 
Can you give us details about the query and the table? Do you want to create
individual EXCEL files, one for each value in the field?
 
I have a table with some 6 fields, one of the fields contains the city.

There are some 50+ cities.

I want to create a unique table for each of those 50+ cities.

The data has to be exported and each attached to a unique email.

Perhaps it would be better to do this outside of Access....
 
Why? Having 50+ separate tables, all with the same data, sounds like a
fairly major violation of database normalization principles.

Strikes me that a parameter query that allows you to specify which city you
want is all you need in this case.
 
I have a similar situation...

in my case... I need to print individual snapshots for every entry in the
query...
 
Agreed!

However, I need a different output file created for each city.

I ALWAYS need to create a unique file for each city... that would be 57
files in total created....
 
That's fine. You need 1 query, and one bit of code.

Assuming you want the files to include the city name as part of the file
name, you'd do something like the following untested air code:

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim rsCurr As DAO.Recordset
Dim strCity As String
Dim strFile As String
Dim strSQL As String

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT CityNm FROM Cities")
Do Until rsCurr.EOF = False
strCity = rsCurr!CityNm
strFile = "C:\Output Files\" & strCity & ".txt"
strSQL = "SELECT Field1, Field2, Field3 " & _
"FROM MyTable " & _
"WHERE City = '" & strCity & "'"

Set qdfCurr = dbCurr.QueryDefs("MyCityQuery")
qdfCurr.SQL = strSQL

DoCmd.TransferText acExportDelim, , "MyCItyQuery", strFile

rsCurr.MoveNext
Loop

rsCurr.Close
Set rsCurr = Nothing
Set qdfCurr = Nothing
Set dbCurr = Nothing
 
THANKS!


Douglas J. Steele said:
That's fine. You need 1 query, and one bit of code.

Assuming you want the files to include the city name as part of the file
name, you'd do something like the following untested air code:

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim rsCurr As DAO.Recordset
Dim strCity As String
Dim strFile As String
Dim strSQL As String

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT CityNm FROM Cities")
Do Until rsCurr.EOF = False
strCity = rsCurr!CityNm
strFile = "C:\Output Files\" & strCity & ".txt"
strSQL = "SELECT Field1, Field2, Field3 " & _
"FROM MyTable " & _
"WHERE City = '" & strCity & "'"

Set qdfCurr = dbCurr.QueryDefs("MyCityQuery")
qdfCurr.SQL = strSQL

DoCmd.TransferText acExportDelim, , "MyCItyQuery", strFile

rsCurr.MoveNext
Loop

rsCurr.Close
Set rsCurr = Nothing
Set qdfCurr = Nothing
Set dbCurr = Nothing
Click to expand...
 
This looks like what I want except I'm not to good with VBA code (and that's
an understatement). <VBG>

So I created a new module and made this a public function and invoke this
from a macro.

I modified the rsCurr statement to point to my file and my field name... as
well as the strSQL...

But nothing happens.

Like I said, VBA is a weak point.

<VBG>

Sorry to ask for what is probably a pretty easy answer...

Thanks again.
 
Back
Top