hello,

I use a huge query to add records to a table (previously emptied) that I export as a CSV. This CSV is now too big. It has information on events. My boss wants me to create a csv for each event name.

So, if I have:

circus1 july 1
circus1 july 2
circus1 july 3
circus2 august 1
circus2 august 2
...

I now need to create circus1.csv and circus2.csv

How can I do it with Access ?

I have tried:

strSQL = "SELECT DISTINCT EVENT.EventName INTO " & [EventName] &" "& _
"FROM EVENT;"

but it doesn't work... :(

have any suggestions ?????

Thank you!!

You will have to run a process.

I would suggest creating a query on the EventName column, and selecting Distinct records, so your query output looks like this:

Circus1
Circus2
etc.

Then run a process that loops through each event in this query. From the process, call your current giant query where EventName = the currnet event name in your process. Alternatively, you can call all the event names from the EventName query into an array, and then loop through the array.

Then base your csv file on the name of the current event name.

SO it can be done quite easily. How much code help do you need? I should have time to help you get this working today.

mmcdonal

Code outline:

Create Connection
Create Recordset on EventName query
MoveFirst
Do Until rs.EOF
  sEvent = rs.Event
  sLink = "[EventName] = & "'" & sEvent & "'"
  sCSVFileName = sEvent & ".csv"
  DoCmd.OpenQuery "qryGiantQuery", , sLink
  DoCmd. ' create text file using sCSVFileName and overwrite any existing copies
  rs.MoveNext
Loop

HTH

mmcdonal