Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2003
    Posts
    17

    Question Unanswered: Sequential numbers for each customer

    I need to create a sequential number for each customer and store them in the same table. For example:

    PBM0001
    PKZ0001
    PBM0002
    PKZ0002
    PKZ0003

    So, the number is sequential within each customer

    The number has 3 parts:
    1-the prefix will always be the letter "P" then,
    2-the next two letters is the customers unique abbreviation then,
    3- comes the sequential number padded to four decimal places.

    The table will also store the unique CustomerID.

    I'm not sure how best to create and store the number. I thought that I would do some sort of Dlookup or DMax of the sequential number based on the customer ID but if I store the whole string then I don't think that I can do the DMax on it because of the letters. So, do I store the parts in seperate fields? And then will the padding of zeros mess up the DMax?
    Thanks for your help, Mitch

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The very easiest is to store just the integer portion (1, 2, 3, 4, ....) as an autonumber and the two letter abbreviation. Whenever you display the data you can prefix this with a P. Something like:

    Code:
    =  "P" & AbbreviationColumn & RIGHT("000" & TheInteger, 4)
    Otherwise check out some of the code in the codebank - I think Izy has a next-number generator in there that gets round several concurrency issues.

    HTH

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    see here
    for one way to do this for a small number of customers (if i remember the demo has 3 categories (= "customers" if you like)

    sequentials for N customers (N variable, large and increasing) cannot use the single-row table model in the demo. instead add a next-in-sequence field to the master customer table (the one where you are getting the XX for your pXXnnnn).

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by mistux
    I need to create a sequential number for each customer and store them in the same table. For example:

    PBM0001
    PKZ0001
    PBM0002
    PKZ0002
    PKZ0003

    So, the number is sequential within each customer

    The number has 3 parts:
    1-the prefix will always be the letter "P" then,
    2-the next two letters is the customers unique abbreviation then,
    3- comes the sequential number padded to four decimal places.

    The table will also store the unique CustomerID.

    I'm not sure how best to create and store the number. I thought that I would do some sort of Dlookup or DMax of the sequential number based on the customer ID but if I store the whole string then I don't think that I can do the DMax on it because of the letters. So, do I store the parts in seperate fields? And then will the padding of zeros mess up the DMax?
    Hi and welcome to the forum. Just from looking at what you have put out there I have just one teeny weeny question. For the UNIQUE customer abbr., what do you do if you have Robert Jones and Richard Johnson??? Might be something you should look into before you get too far down the road. i.e...what then makes each one UNIQUE?

    Have a nice one,

    BUD

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    "Why?" is all I keep thinking here. You're making work for yourself that is only going to lead to trouble. Such a field can't serve any purpose I can see.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Pimary key values (something that you can use to uniquely identify each row) should only have meaning if the key is natural. Run with Mr Flumps advice if you really have to do it this way; but to be honest an AutoNumber field would sufice for you.
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Pimary key values (something that you can use to uniquely identify each row) should only have meaning if the key is natural. Run with Mr Flumps advice if you really have to do it this way; but to be honest an AutoNumber field would sufice for you.
    Yes. To the OP - the autonumber on it's own is sufficient to uniquely identify the record. This should be the PK and what is used "under the hood" by the database (e.g. in relationships). The other part is just a method you can generate your codes from the underlying data - presumably this will be a customer number or something that will make it onto reports etc.

  8. #8
    Join Date
    Oct 2008
    Posts
    1
    This is my first post.
    I also need to have a unique and automated sequential number system on the web site I am developing. When people order something from it I need to be able to identify who ordered what and also they can quote their unique number when making Internet payment so I can marry everything up at the end.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so just have a transaction ID (which is an autonuber ID).

    one thing to be aware of on websites is that some customers may get very wary of they see a low order number (ie threre's not many people buying form here, you also expose the level of business you are doing to your suppliers and comptitors. you can get round that by generating a transaction ID for each visitor. each visitor on each visit ses the transaction number is rising, and thinks that the site is doing more business. One site I know foudn supplies took them more seriously whne they perceived transation volumne at the site were significantly higher than sales actually were.

    you can dress it up however you like, even add lipstick to it if you wish. A customer doesn't need a unique order number in ascending sequence... I you asked them I'd guess they couldn't give the proverbial providing they get their goods.

    techniques Ive seen to add lipstick to the the number is to add a prefix, a suffix, some alpha codes, providing you an easily and quickly identify the true code all is well
    Ive see a number added to the front of the transaction ID ie transacton 825 becomes 780100825



    Ive seen a one or two character alpha code prefixed to indicate the year & month, Ive seen random number tacked onto the end, ive seen many ways of masking the level of business.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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