How do I add invoice line numbers when importing

[sfp]

For every item that appears on an invoice I import from my supplier, I want
to allocate a line number. Together with the invoice number these should
form a composite key. I can figure out how to do this through a form, which
is great for suppliers who cannot give me a csv file, but to import a bulk
load of data I need to write code and am stuck!

 

[Ken Snell MVP]

Assuming you can use an autonumber field as the "line number", create a
table with the fields from your "bulk data file", and then add an autonumber
field as the last field. Import the data to this table, and each line will
get a unique number in the autonumber field.

 

[sfp]

Thanks Ken! How would I get it to restart numbering for each new invoice?
Say Inv2001 has 4 lines on it, and Inv2002 has 8 lines on it, I would want
numbering from 1-4 and then 1-8?

 

[Ken Snell MVP]

You wouldn't. But, you can use an append query to copy the data from this
table into your permanent table, and use the Autonumber field to calculate a
"ranking" for each invoice:

INSERT INTO PermanentTablename
(Field1, Field2, InvoiceNumber, InvoiceLineNumber)
SELECT Field1, Field2, InvoiceNumber
(SELECT Count(*) FROM InterimTableName AS T
WHERE T.InvoiceNumber = InterimTableName.InvoiceNumber
AND T.AutoNumberField >= InterimTableName.AutoNumberField)
AS InvoiceLineNumber
FROM InterimTableName;