Re: Key fields-an autonumbered contract# and a new text line_item#

Tech-Archive recommends: Fix windows errors by optimizing your registry

On Mon, 25 Jul 2005 15:25:01 -0700, "gg"
<gg@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

>I currently have 2 primary key fields: 1) contract_no and 2) line_item_no
>(currently autonumbered) for my Main Contract table and Contract Detail
>Table. However, now the users want a user entered text field line_item_no
>(still a key field) because they may have several line_item 1's: ie. for
>contract_no=1; but now with line_item_no="1-oa" (for original agreements),
>another record with line_item_no="1-a1" (for amendment1), and yet another
>line_item_no="1-a2" (amendment2), etc.
>
>How would you recommend doing this since now the contract_no is
>auto-numbered, and line_item_no=text and a user-entered field, but still need
>to be primary keys? Should I just keep the current contract_no as
>auto-numbered and keep the autonumbered line_item_no but add a new field that
>users enter as the 1-oa, 1-a1, etc.?

I would really suggest making this three separate fields: Contract_No,
Line_Item_No, and Extension, and combine them for display. I would
also suggest NOT using Autonumber for any of these; autonumbers always
have gaps, cannot be edited, and are best kept "under the hood" rather
than displayed to users. Your Primary Key could either be a
three-field composite key, or (especially if you'll be linking this
table to further tables) an autonumber "surrogate key" that will be
kept invisible. If you do the latter, create a unique three-field
index on the Contract/Line/Ext fields.

You can increment Line_Item_No using some very simple VBA code on your
data entry form; it sounds like the Extension will need to be manually
entered.


John W. Vinson[MVP]
.

Relevant Pages

  • Re: Non-unique AutoNumber
    ... Sounds like the same effect could be achieved by creating a "Master ... Contract" table where Autonumbers were unique. ... Autonumber would get turned off but the unique Master# + Type ... Field1 is a base contract ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Key fields-an autonumbered contract# and a new text line_item#
    ... If I can keep the autonumber for the contract number, ... from autonumber to text and how is that done? ... >>to be primary keys? ... > three-field composite key, or (especially if you'll be linking this ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Non-unique AutoNumber
    ... I just wanted a second opinion before I told the original designer that I ... could not think of any reason to use a non-unique AutoNumber. ... Field1 is a base contract number, ... Having an AutoNumber to ensure that each new record got a new base ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Non-unique AutoNumber
    ... Field1 is a base contract number, ... > guess you do need to hold onto the [Field1] values. ... Having an AutoNumber to ensure that each new record got a new base ... > base contract number are more closely related than ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Hpw best to link item & detail when items are very different
    ... tblItems ... itmItmID PK autonumber ... Item Telephone would have details of Color, Cordless y/n, Answer y/n ... A details table for each Item type makes the most sense but I am not sure how to display the one I need using forms ...
    (microsoft.public.access.tablesdbdesign)