Assigning Sequential Numbering

[Kipper1]

I am using Access 2003. I need to create a
database to transfer accounts receivable data from one system (A) to another system (B) via text files for a friend's business.

I have created a database (entitled AR) that has several tables created and updated from append queries. I have it set to update all the tables by using macros to delete and append the tables with updated information, and then set to export the tables to text files with the exact layout required by System B. Everything with the database works fine, except for one aspect.

In the "Payment table" which lists all payments for all invoices, I am required (by system B) to assign sequential numbering for each invoice payment. For example, if there was 5 partial payments for invoice #123, I would have to assign "1" to the first payment, 2 to the second payment, etc into a column entitled "PaymentNum." Then for invoice #124 which has 2
payments, I would have to assign 1 to the first payment, 2 to the second payment in the same "PaymentNum" column. My queries are already set to sort the table by invoice numbers in ascending order so that the sequential numbers
can be assigned.

I assume I can write a function in Visual Basic in order to include it in my append query, but I am unsure whether this can even be done, or how to execute the code to assign these numbers. I have tried a couple of books but they don't seem to have any specifics on this exact procedure.
Any advice or guidance would be greatly appreciated.

[gershwyn]

You can do this using the DCount function in an update query, assuming that you have a way to easily differentiate between records. For example, if your table has an autonumber field called "RecordID", this SQL will update the payment numbers:

UPDATE PaymentTable SET PaymentNum = DCount(1, "PaymentTable", "InvoiceNum = " & [InvoiceNum] & " AND RecordID <= " & [RecordID]);

That is counting how many records exist that a) have the same invoice number as the current record and b) have a recordID that is less than or equal to the current record, and using that as the payment number.

[patjones]

How are you discerning what the "first", "second", "third", etc. payments are? By using payment dates, or maybe some kind of record number as gershwyn suggested? This would clarify things a bit.

Pat

[NeoPa]

Some information as to the data you're working with would certainly be necessary before providing a matching solution, but certainly the concept illustrated in Gershwyn's post is fundamentally a good one.