Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2015

    Unanswered: Sequence number -- not Autonumber.

    I am still new to the Access 2013 environment. I am building a database where a Quote Number would have xx number of Quote Items.

    Table 1
    Quote_Number Autonumber
    Quote_Date Date [=Date()] and locked on my form

    Table 2
    ID Autonumber
    Quote_Number Interger -- Long Interger -- (Foreign Key from Table 1)
    Item_Number {my sequential number -- Depending on the number of records for Quote_Number in this table}

    How can I get my 'Item_Number" to be in sequence when grouped for 'Quote_Number?"

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    delete the ID. FWIW its generally advised to use an autonumber column where there is either:-
    no obvious existing column (or columns) that would make a good primary key ( a so called candidate key)
    there is an existing column, or columns that would make a good key, except that
    its too long / too complex / to fiddly
    or is liabel to change and there fore probbaly not suitablle as a PK.

    in this case the combination of quote number and ItemNumber should be sufficient. Indeed it woudl be preferable to your current setup as
    1) it would enforce a relationship beteween table 2 and the quotations table. (ie there must be a pre existign quote number in tabel 1 bnefore you can enter rows in table2, you cant have a quote number in table 2 for which there isn't a matching quote number in table1

    2) it woudl enforce that you can only ever have one quote + item number combination (so no duplicate item numbers for a specific quote.

    so how to get an ascending sequential number . there's plenty of examples on this forum and elsewhere. google

    things to bear in mind
    1) in a multi user environment you need totake design decisions on what to do if more than one person attemtpst o add an itme to the same quote at the same time.
    2) you need to make a concious choice on deleting rows (do you renumber existing itmes, do you soft delete them, do you insert new tiems in gaps and so on.)
    3) do you use soem form of table /record locking

    at the very least you can get the current highest value using the domain function dlookup

    I'#d suggest that you wrap the whole process into a fucntion that returns the next available number
    pass the quote nmumkber as an incoming parameter, and return the next available as a number as a parameter fromt he fucntion call.
    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