Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2004
    Posts
    126

    Unanswered: How can I assign serial numbers in multiple ranges

    I know this is not the best database practice, however, it is a business requirement.

    Our orders are assigned order #s in ranges based on what product they are for, let's say products A-E, i.e.

    Product A: Order #s 000000-099999
    Product B: Order #s 100000-199999
    Product C: Order #s 200000-299999
    Product D: Order #s 300000-399999
    Prodcut E: Order #s 400000-499999

    I want to store all of these orders in one Orders table because they're all...orders. How can I assign these unique order #s in these different ranges?

    I am using an Access 2003 Data Project using SQL Server 2000 as the backend and am fluent in VBA and SQL, I just need to be pointed in the right direction.


    The other piece to this, which isn't as big a concern is that orders get entered in lump sums, so say a customer makes an order for 50 shipments, I would like to be able to assign them 50 order#s based on the next 50 consecutive #s in that range. If I can do it all at once, that would be great, but one at a time works for now, as long as they are consecutive, but by product.

    I think that is clear, but I can elaborate if it will help anyone help me.


    Thank you!!!

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by grrr223
    I know this is not the best database practice, however, it is a business requirement.

    Our orders are assigned order #s in ranges based on what product they are for, let's say products A-E, i.e.

    Product A: Order #s 000000-099999
    Product B: Order #s 100000-199999
    Product C: Order #s 200000-299999
    Product D: Order #s 300000-399999
    Prodcut E: Order #s 400000-499999

    I want to store all of these orders in one Orders table because they're all...orders. How can I assign these unique order #s in these different ranges?

    I am using an Access 2003 Data Project using SQL Server 2000 as the backend and am fluent in VBA and SQL, I just need to be pointed in the right direction.


    The other piece to this, which isn't as big a concern is that orders get entered in lump sums, so say a customer makes an order for 50 shipments, I would like to be able to assign them 50 order#s based on the next 50 consecutive #s in that range. If I can do it all at once, that would be great, but one at a time works for now, as long as they are consecutive, but by product.

    I think that is clear, but I can elaborate if it will help anyone help me.


    Thank you!!!
    Create a table like:

    ProductID
    Starting Order Range
    Ending Order Range
    Next Order #



    You'll need to test if the next order # exceeds the ending range # ...

    Notice that this will accomodate your need for "batch" processing too ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Feb 2004
    Posts
    126
    I like that , it's so simple too. Then all I have to do is update that table whenever I create a new order, it's great.

    Actually...that kinda leads me in another direction. Or just a different way of implementing what you suggested.

    Have the table with the product, min range, max range, and then use a query to Select the Max(order_no) that is in those ranges to create a view that looks like what you suggested, that way it can't get out of sync.

    AND, by setting the order_no column to be the primary key (no duplicates), even if this process skips a number or two somehow, at least there won't be any order number assigned to two customers.

    I will definitely try that, and let you know how it works.

    Thank you

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Well, as you're fluent in VBA and SQL, might I suggest pulling max + 1 values within a given range?

    Eg for product B:

    SELECT MAX(order_id) + 1
    FROM Order
    WHERE order_id BETWEEN 100000 AND 199999

    I would probably place this in a VBA function so you don't have to screw around with specifying the range all the time.
    Code:
    Public Function GetID (ProductType As String)  As Integer 'Are you using a string?  I dunno
    Select Case ProductType
    Case "A"
       GetID = DLookup("OrderID", "Orders" "OrderID = (SELECT MAX(OrderID) FROM Orders WHERE OrderID BETWEEN 0 AND 99999)") + 1
    Case "B"
        GetID = etc etc
    End Select
    Anywho, this would allow you to pull product numbers by passing the product type to your public function.
    Last edited by Teddy; 05-05-04 at 12:32.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by grrr223
    I like that , it's so simple too. Then all I have to do is update that table whenever I create a new order, it's great.

    Actually...that kinda leads me in another direction. Or just a different way of implementing what you suggested.

    Have the table with the product, min range, max range, and then use a query to Select the Max(order_no) that is in those ranges to create a view that looks like what you suggested, that way it can't get out of sync.

    AND, by setting the order_no column to be the primary key (no duplicates), even if this process skips a number or two somehow, at least there won't be any order number assigned to two customers.

    I will definitely try that, and let you know how it works.

    Thank you
    Yours and Teddy's thought I don't understand ... My scheme has as a column THE NEXT AVAILABLE order # ... Why get the MAX? That's just a waste of time - let alone the individual #'s themselves are not going to be in this table ...
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    MAX(field) + 1 IS the next available order number. If you scroll right in the code, you'll see I'm incrementing the max value.

    The order numbers themselves are already present, why create another table, and therefore bloat just to keep track of information you already have available?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Teddy
    MAX(field) + 1 IS the next available order number. If you scroll right in the code, you'll see I'm incrementing the max value.

    The order numbers themselves are already present, why create another table, and therefore bloat just to keep track of information you already have available?
    Ok. You have a valid point and a different scheme for doing it ... There is one drawback tho (I had this same problem years ago ...) and that is WHAT are you gonna do when the range is exceeded???? Where are you gonna go?
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Teddy
    The order numbers themselves are already present, why create another table, and therefore bloat just to keep track of information you already have available?
    As for this: What happens to this query when you have say ... 100,000 records? SLOW?

    Yes you have another table with few records but the couple of extra bytes are worth it ... Let alone the fact that the ranges are defined within the table and not in code so if you need to adjust them it's easier ...
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That is outside the scope of his parameters and question, and a question that could only be answered by the party requesting the work.

    Unless you are referring to adding additional ranges, in which case it's a simple matter of adding another case evaluation, much the same as you would need to add another range specification to your reference table.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by M Owen
    As for this: What happens to this query when you have say ... 100,000 records? SLOW?

    Yes you have another table with few records but the couple of extra bytes are worth it ... Let alone the fact that the ranges are defined within the table and not in code so if you need to adjust them it's easier ...
    It's not slow at all providing you've indexed your key fields like any good dba who cares for over 100,000 records should be doing.

    Anywho, both solutions are valid, at this point it's a choice between ideology. Either or is fine.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Teddy
    That is outside the scope of his parameters and question, and a question that could only be answered by the party requesting the work.

    Unless you are referring to adding additional ranges, in which case it's a simple matter of adding another case evaluation, much the same as you would need to add another range specification to your reference table.
    That's would be rolled into the creation of the new product ID ... As for your first point, I think not. It is not outside of his question's scope ...

    It's a rather moot point. He'll do whatever he wants anyways ... No sense in getting myself riled up ...
    Back to Access ... ADO is not the way to go for speed ...

  12. #12
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Teddy
    It's not slow at all providing you've indexed your key fields like any good dba who cares for over 100,000 records should be doing.

    Anywho, both solutions are valid, at this point it's a choice between ideology. Either or is fine.
    Point taken. This is SQL Server we're talking about ... Yes, light years ahead of Access in query performance ...
    Back to Access ... ADO is not the way to go for speed ...

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You know, I was just thinking about the performance tip... You would probably be better off coding this as a UDF server side, then call it with a trigger so you never have to think about it.
    Last edited by Teddy; 05-05-04 at 13:19.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  14. #14
    Join Date
    Sep 2003
    Location
    NJ
    Posts
    5

    Just a quick question.

    I'm not that familiar with SQL server, but, in Access, considering that his first group starts out with a zero, wouldn't these numbers have to be stored as text so that in the first group he wouldn't lose the leading zeros? Wouldn't that make the automatic assignment of numbers a little more complex?

  15. #15
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by grnzbra
    I'm not that familiar with SQL server, but, in Access, considering that his first group starts out with a zero, wouldn't these numbers have to be stored as text so that in the first group he wouldn't lose the leading zeros? Wouldn't that make the automatic assignment of numbers a little more complex?
    NO. Ever hear of the Format function???? Keep the numbers as longs and don't worry about it...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •