Hi Haviv,
Import your existing data into a new table in Access, using File> Get
External Data > Import. Let Access automatically add an ID autonumber field.
Use the default name of "Sheet1" (without the quotes).
Notes:
The records must be in the proper order. I cleaned up your data first, using
Excel, to get it into four columns. However, this manual cleanup would not be
practical if one needed to do it on a large recordset. You may need to choose
fixed width, assuming your existing data is not delimited.
After you have a table in Access with four fields, which looks like the data
that your reporting system outputs, copy the table and paste the structure
only to a new table named "tblFullData". Create the following query, and
name it "Query1":
SELECT Sheet1.ID, Sheet1.InvoiceNumber, Sheet1.blNumber,
Sheet1.FreightItem, Sheet1.Quantity
FROM Sheet1
ORDER BY Sheet1.ID;
Note 2: This query is included just for good measure. It specifically uses
an ORDER BY clause on the ID autonumber primary key. I *think* the records
would still be order by primary key without this query, but this simply
ensures that they will be in PK order.
Create a new module. Set a reference to the Microsoft DAO 3.6 Object
Library, if one is not already set in your database. Copy the code shown
below, and paste it into your new module:
Option Compare Database
Option Explicit
Sub RewriteRecords()
On Error GoTo ProcError
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim varInvoiceNumber As Variant
Dim varBlNumber As Variant
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("Query1", dbOpenSnapshot)
Set rs2 = db.OpenRecordset("tblFullData", dbOpenDynaset)
rs1.MoveFirst
With rs1
Do Until (.BOF Or .EOF) = True
With rs2
varInvoiceNumber = rs1!InvoiceNumber
varBlNumber = rs1!blNumber
.AddNew
!InvoiceNumber = varInvoiceNumber
!blNumber = varBlNumber
!FreightItem = rs1!FreightItem
!Quantity = rs1!Quantity
.Update
rs1.MoveNext
Do Until Not IsNull(rs1!InvoiceNumber)
.AddNew
!InvoiceNumber = varInvoiceNumber
!blNumber = varBlNumber
!FreightItem = rs1!FreightItem
!Quantity = rs1!Quantity
.Update
rs1.MoveNext
Loop
rs1.MovePrevious
End With
rs1.MoveNext
Loop
End With
MsgBox "All Done!", vbInformation, "All records have been processed..."
ExitProc:
'Cleanup
On Error Resume Next
rs1.Close: Set rs1 = Nothing
rs2.Close: Set rs2 = Nothing
db.Close: Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure RewriteRecords..."
Resume ExitProc
Resume
End Sub
Click on the Save button to save the code. Name the module any name except
RewriteRecords. A good choice would be "basRewriteRecords". Click on Debug >
Compile ProjectName. Hopefully, the code will compile for you, without any
errors (it compiled okay if this menu item becomes "grayed out"). With your
blinking mouse cursor located anywhere within the procedure, press the F5
button. If all goes okay, the tblFullData table should include your
re-written records.
Note: Your data indicates that when InvoiceNumber is null, BlNumber is also
null and each time a new InvoiceNumber is encountered, a new BlNumber is
used. This is the assumption that the code uses. Also, I renamed the field
"freighitem" to "FreightItem". The field name is not case-sensitive, but it
does include an extra "t".
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
