New Table form one column

Hi Everybody

I have a table with three columns

http://homepages.paradise.net.nz/davidshe select Access link

I have set out the original file and the required result. The original file contains up to 15 difference types of records,and Record Invoice is just one of them. The record types vary in the number of Lines.

The number sequence is created in excell and keeps the records in order.


C1 C2 C3
Record Invoice 10515 1
Record Invoice 10515-0015 2
Record Invoice 15/1/2004 3
Record Invoice $75.00 4

I would like to automate the conversion process to automatically create the new table,

Could I please have help with the code and the type of query required.

Thanks David

Hi David,

If this is just a one time make a table to transfer the data from your existing table, then I can suggest a method that will be pretty quick and easy to do. All of this assumes of course that your C3 column is your unique number for each field of your new table. Example 1 will always identify the Code. 2 will always identify the Inv No. 3 will always identify the Date and 4 will always identify the Amount. If that is correct, then create your new table with the fields layed out like you want. Save the table and create a select query based on this new table. Next create a select query based on your old table and use the C3 column to filter by. For example filter by 1 will display all the records with the Code. Then simply copy the column and paste it into your other query in the appropriate column. Do the same thing again only filter by 2 and then copy and paste those records into the appropriate column and so forth. This is pretty easy to do and will be fairly quick. I could write you the code to do this by looping through the recordset and updating your new table etc., but I think you will find this method much quicker and then you can delete the queries when finished.

Just a simple suggestion
lwells