Count Record Sequentially

[DBFIN]

We bill each customer four equal installment invoices. The installment data is stored in a table (please see file attachment). I need to assign an installment sequence number for each invoice in the table. Each customer would have installment sequence number of 1,2,3,4 without exception.

How can I assign the installment sequence number for each customer using a standard expression or function. I won't be using any code to accomplish this.

 

[The_Doc_Man]

Let's say you have a customer "master" invoice number as the primary key of your invoice table (the one for which you will have 4 installment sub-invoices). For simplification just to show the principle involved, let's say that if the customer makes two separate purchases, they get two sets of invoice installments also separately. (From here, there are ways to "roll them up..." - but first things first.)

Look up DCount function. What you want is the DCount of the installment records associated with a given invoice master number - and add 1 to the count. So if there are no installements on record, DCount will return 0 - add 1 and that is the new installment number. You can follow the logic yourself.

The DCount will be DCount( "[invoicenum]","tablename","[invoicenum]=" & CStr( [ { whatever is your invoice number source } ] ) & """" ). NOTE: This assumes the invoice number is actually a number and not a mixed digit-something-else type of string.

Presuming that the "master" invoice numbers are unique, that's all it takes. If the invoice numbers can repeat for each customer, you've muddied the waters.

OK, to roll these up so that you could send one mailing of installment notices, OR to roll up the installments for multiple invoices into a single bill, you have to start assuming there is a customer number and that you can build a query that includes both the customer number and invoice numbers, group by customer, order by invoice, and then you could write a report based on the grouped-and-ordered query to provide page breaks on customer number, treat invoice installments as detail items. It would also be up to you to change the status of the records so that you could mark an invoice as PAID (probably a suitable yes/no or multi-state flag) when the last installment has been paid for that invoice. Which means your filtration query would include the state of each invoice as being "balance pending" or whatever you call your version of that.

Hope that wasn't too convoluted. I think on the fly and shoot from the hip a lot.

 

[DBFIN]

Counting Records Sequentially

I created Query A which reads ARFILE Table. Query A has an INSTALL SEQ field which counts the INVOICE field in the ARFILE table as follows. The INVOICE field is a unique invoice number for each installment.

INSTALL SEQ: DCount("INVOICE","ARFILE")

The field returns the total ARFILE table record count of 10,846 for each record instead of counting 1,2,3,4 for each customer.

 

[The_Doc_Man]

You made the sequence numbers for the table records as a whole. You must differentiate between records for different invoices.

Treating your dataset as a whole will get you counts for the dataset as a whole.

You must have a table somewhere that says "Installements" for which the keys are "InvoiceNum" and "InstallmentNum" (or equivalent in your naming system). Then you have to group that by InvoiceNum and count the InstallmentNum records. I guess I wasn't as clear as I should have been.