sequential numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can someone help me with an expression for a query that will show groups of
invoice numbers that are numbered sequentially (25601, 25602, 25603, etc.)
for any particular vendor?
 
Can someone help me with an expression for a query that will show groups of
invoice numbers that are numbered sequentially (25601, 25602, 25603, etc.)
for any particular vendor?
Click to expand...

Could you post a bit more detail, say an example of the existing data
and what you want to see?

John W. Vinson[MVP]
 
Sure. I have a table with all accounts payable transactions for 2005. This
includes vendor number, invoice number, payment amount and payment date.
What I want to do is see if any vendor provides a service or product to our
organization exclusively. To identify these vendors I want to test their
invoice numbers to see if they occur in numerical sequence, i.e., 25601,
25602, 25603, etc.
 
Perhaps you can solve that problem with

SELECT VendorNumber
FROM AccountsPayable
GROUP BY VendorNumber
HAVING Count(InvoiceNumber) = Max(Val(InvoiceNumber)) -
Min(Val(InvoiceNumber)) + 1
 
Finding non-sequential numbers in a vendor's invoices does not prove
anything, because you do not know the vendor's business rules for assigning
numbers. They could be based on dates, or phases of the moon, or the outdoor
temperature at the time of invoice creation.
 
Back
Top