invoice number

  • Thread starter Thread starter J.J.
  • Start date Start date
J

J.J.

hi i have a query where i need formula to numerate records from minDate to
maxDate reffering to CUSTOMER_ID to make a valid invoice number.
here an example:
CUSTOMER_ID=1
PAY_DATE=08/07
PAID=280,00$
INVOICE_NR=1

CUSTOMER_ID=1
PAY_DATE=08/07
PAID=244,00$
INVOICE_NR=1

CUSTOMER_ID=1
PAY_DATE=08/07
PAID=3054,00$
INVOICE_NR=1

CUSTOMER_ID=1
PAY_DATE=10/07
PAID=244,00$
INVOICE_NR=2

so first three records should have INVOICE_NR=1 because they're all one the
same invoice and the 4'th record should have the INVOICE_NR=2.
the next problem is that when:
CUSTOMER_ID=2
PAY_DATE=06/07
PAID=248,00$
INVOICE_NR=1
so when the CUSTOMER_ID=2 the minPayDate should have the INVOICE_NR also 1.
Hope you could help me. Thanks a lot
J.J.
 
Dear JJ:

This sounds like a ranking query to me, with numbering following the
number of values found in PAY_DATE and starting from 1 for each
CUSTOMER_ID.

SELECT CUSTOMER_ID, PAY_DATE, PAID,
(SELECT COUNT(*) + 1
FROM (SELECT DISTINCT PAY_DATE
FROM YourTable T2
WHERE T2.CUSTOMER_ID = T.CUSTOMER_ID) T1
WHERE T1.PAY_DATE < T.PAY_DATE) AS Rank
FROM YourTable T

I have some trouble thinking of this as an Invoice Number. Some
reasons are:

1. The number repeats, counting from 1 for each customer. Not
normally what an invoice is.

2. It assumes there could never be 2 invoices for the same customer
in the same day. Also not typical.

3. The numbering would change when more data is entered. A customer
with two PAY_DATEs would have invoices 1 and 2. If another row were
added to the table with a PAY_DATE between the two PAY_DATES already
in the table, then the rows previously associated with invoice number
2 would now be invoice nubmer 3. Not a good thing to happen to an
invoice number in most business settings.

I mention these things so you can see the side-effects of what I
understood you were wanting.

Is this anywhere close to what you want?

Please change "YourTable" to the actual name of your table and check
my spelling of the columns used.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
thanx a lot but it didn't work or i don't know how to use it. i need it to
use it from the table then from one query that holds informations from three
tables. is it possible?
 
Back
Top