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

[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?

 

[Ken Snell \(MVP\)]

Probably not... but give us more details please.

 

[Gary J. Dikkema]

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.

 

[Ken Snell \(MVP\)]

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?

 

[Gary J. Dikkema]

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....

 

[Douglas J. Steele]

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.

 

[Rita]

I have a similar situation...

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

 

[Gary J. Dikkema]

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....

 

[Douglas J. Steele]

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

 

[Gary J. Dikkema]

THANKS!

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

 

[Gary J. Dikkema]

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.