Moving data from one table to another.

[joeythelips]

Hi,

I posted a thread yesterday but i didn't really explain it clearly.
I have 2 tables: StartupBills and Startupperm.
StartupBills imports data from an excel spreadsheet.
I then transfer this data to Startupperm, and delete all from StartupBills.
The sql i use to transfer is as follows:

readfilename1 = InputBox("Please enter the date of the startup file", "Startup File&quot
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "StartupBills", "C:\My Documents\test1\STARTUP " & readfilename1 & ".xls", True
sSql = "insert into StartupPerm ([headername],[Meter Ref#],[Customer Name],[Site Address],[Profile],[Contract Date],[Reading Type],[Meter Seq#],[Meter No#], [Multiplier],[Start Reading],[Meter Type],[EAC], [Refid]) select '" & Ref1 & "',[Meter Ref#],[Customer Name],[Site Address],[Profile],[Contract Date],[Reading Type],[Meter Seq#],[Meter No#], [Multiplier],[Start Reading],[Meter Type],[EAC], [Refid] from StartUpBills"
CurrentDb.Execute sSql
sSql = "delete * from startupBills"
CurrentDb.Execute sSql

I want to move this data across exactly as it appears in the startupbills table. But there is one problem:
the data in the startupbills table appears like this:
Field1 Field2 Field3
John 12/12/66 seq1
seq2
seq3
Paul 1/1/77 seq1
seq2

Then when i append it to the perm table, it appears with all the blank field1's together at the start of the table.

Does anyone know how to overcome this?

 

[Chopper]

If you create an ID field in the Perm Table that is autonumber, as the first field in the table, your data will appear in the order you append it.

The reason all the blank fields are showing up first is likely that you do not have a key or index for the table so all of the data is sorting on the first column, then the second column, etc.

-Chopper