Create multiple text files based on customerID

[ezra]

I need to run a monthly audit on a customer invoice database that prints off order totals per customer. Ex. If customer 1234 has 4 orders for the month I want to see the text file as follows: 1234 10.21 101.22 22.11 82.01 (The OrdersTotaltbl already stores the order totals and CustID). The problem for me is that I need to output a separate file for each customer file saved as the CustID. There is currently a list of 200+ regular customers ordering at least twice a month. What would be the best way to set this up?

[Denburt]

Do you already have a routine for exporting at least one customer? If so that would save some time here. If you do have a routine for that then you would just need to open the recordset and use a loop statement to cycle through each of the clients and run your export routine at that time. Something like the following.

set rs = db.openrecordset("clientsOrders")
 
Do until rs.eof
  strQuery = "Select * from clientsOrders Where ClientID = " & rs!ClientID
    Db.Execute _
    "SELECT * INTO [Text;FMT=Delimited;HDR=" & strHdr & ";DATABASE=" & strPath & ";].[" & StrFile & "#csv] FROM " & strQuery, _
    dbFailOnError
rs.movenext
loop

[ezra]

Do you already have a routine for exporting at least one customer? If so that would save some time here. If you do have a routine for that then you would just need to open the recordset and use a loop statement to cycle through each of the clients and run your export routine at that time. Something like the following.

set rs = db.openrecordset("clientsOrders")
 
Do until rs.eof
  strQuery = "Select * from clientsOrders Where ClientID = " & rs!ClientID
    Db.Execute _
    "SELECT * INTO [Text;FMT=Delimited;HDR=" & strHdr & ";DATABASE=" & strPath & ";].[" & StrFile & "#csv] FROM " & strQuery, _
    dbFailOnError
rs.movenext
loop

Currently I have nothing in place. This is something new the boss has thrown at me! Thanks for this. It does give me a better idea of what I need to do.

[Denburt]

Currently I have nothing in place. This is something new the boss has thrown at me! Thanks for this. It does give me a better idea of what I need to do.

Your quite welcome glad I could help. I know I left a lot out so if you need any assistance in implementing this just post back. O.K. here is a little more and should get you a bit closer.

   1.
      set rs = db.openrecordset("clientsOrders")
    strHdr = "Yes"
    strPath = "C:\Client Support\DailyReport\"
 
      Do until rs.eof
StrFile = Format(Date(), "yyyy-mm-dd") & " " & RS!ClientName
   strQuery = "Select * from clientsOrders Where ClientID = " & rs!ClientID
   Db.Execute _
   "SELECT * INTO [Text;FMT=Delimited;HDR=" & strHdr & ";DATABASE=" & strPath & ";].[" & StrFile & "#csv] FROM " & strQuery, _
             dbFailOnError
         rs.movenext
      loop