By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,491 Members | 1,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,491 IT Pros & Developers. It's quick & easy.

Create multiple text files based on customerID

P: 11
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?
Dec 5 '07 #1
Share this Question
Share on Google+
3 Replies


Denburt
Expert 100+
P: 1,356
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.

Expand|Select|Wrap|Line Numbers
  1. set rs = db.openrecordset("clientsOrders")
  2.  
  3. Do until rs.eof
  4.   strQuery = "Select * from clientsOrders Where ClientID = " & rs!ClientID
  5.     Db.Execute _
  6.     "SELECT * INTO [Text;FMT=Delimited;HDR=" & strHdr & ";DATABASE=" & strPath & ";].[" & StrFile & "#csv] FROM " & strQuery, _
  7.     dbFailOnError
  8. rs.movenext
  9. loop
Dec 5 '07 #2

P: 11
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.

Expand|Select|Wrap|Line Numbers
  1. set rs = db.openrecordset("clientsOrders")
  2.  
  3. Do until rs.eof
  4.   strQuery = "Select * from clientsOrders Where ClientID = " & rs!ClientID
  5.     Db.Execute _
  6.     "SELECT * INTO [Text;FMT=Delimited;HDR=" & strHdr & ";DATABASE=" & strPath & ";].[" & StrFile & "#csv] FROM " & strQuery, _
  7.     dbFailOnError
  8. rs.movenext
  9. 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.
Dec 5 '07 #3

Denburt
Expert 100+
P: 1,356
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.

Expand|Select|Wrap|Line Numbers
  1.    1.
  2.       set rs = db.openrecordset("clientsOrders")
  3.     strHdr = "Yes"
  4.     strPath = "C:\Client Support\DailyReport\"
  5.  
  6.       Do until rs.eof
  7. StrFile = Format(Date(), "yyyy-mm-dd") & " " & RS!ClientName
  8.    strQuery = "Select * from clientsOrders Where ClientID = " & rs!ClientID
  9.    Db.Execute _
  10.    "SELECT * INTO [Text;FMT=Delimited;HDR=" & strHdr & ";DATABASE=" & strPath & ";].[" & StrFile & "#csv] FROM " & strQuery, _
  11.              dbFailOnError
  12.          rs.movenext
  13.       loop
Dec 5 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.