Counting Line Numbers

  • Thread starter Thread starter Josh P
  • Start date Start date
J

Josh P

I have a database of customers and their orders. there is
a line number field for customers that order multiple
items. the first product gets a line number of 1, the
second 2, etc.

each order has a unique ID, and each product refers to a
unique ID (if there's an order of 2 items, both refer to
that unique ID).

what is the best way to go about counting the line number?
I was thinking that I could have a running total going per
unique ID. if there are 5 items with the same unique ID,
those 5 will get line numbers 1, 2, 3, 4, 5, respectively.

I'm open to other ideas as well, but I figured this would
be the easiest way to solve the task.

Thanks!
 
Dear Josh:

This is a perrennial problem. Analysis should probably include what
to do if a new line is inserted, and if an existing line is deleted.

In many applications, there are other tables that depend on the unique
key of the OrderDetail table (as I would call this). This may be a
factor in how we build this.

With deletions, there may be gaps in the numbering. I would not
recommend renumbering them when a deletion is made. The same
principle applies to how to do insertions.

To answer you simpler question, I would query the order to find the
MAX() of the line numbers, then just add 1 to that. There's no need
to store the current value of the next number, as this can be easily
derived.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top