Extracting Data

[Rjc8513]

I have a main table containing 5000 records. Each record consists of ID No., Year, Month, Workhours. The data looks like this:

ID No. Year Month Workhours
361234 2001 01 625
361234 2001 02 595
364218 2001 01 722
364218 2001 02 688

Using code, how can I extract the data for 361234 only and output it to a text file, then for 364218, looping thru each ID No until the entire main table has been "parsed" into separate text files? Note: this month, 361234 may consist of two records, next month it may consist of 6.

Thanks.

Richard...

 

[LonnieJohnson]

Dim db As Database
Dim rst As Recordset
I have a procedure that will create a text file on whatever drive you desire. Each text file will be a record from a table based on a unique identifier (IdNo in your case). This process interates thru your entire recordset. The name of the text file will be the same as the name of the unique identifier. Thus, the first text file would be called 361234. Here is the code below. I have a sample database I can send you that has a command button on a form that executes this to your C drive with your test data from above. If you'd like I can email it to you.



Set db = CurrentDb
Set rst = db.OpenRecordset("customer&quot

Do Until rst.EOF
db.CreateQueryDef rst!IdNo, "SELECT * FROM CUSTOMER WHERE IdNo = '" & rst!IdNo & "';"
DoCmd.TransferText acExportDelim, , rst!IdNo, "c:\" & rst!IdNo & ".txt"
DoCmd.DeleteObject acQuery, rst!IdNo
rst.MoveNext
Loop
ljprodev@yahoo.com
Professional Development
MS Access Applications