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

[gg]

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.?

Just need someone's advice.

Thank you!
gg

 

[Brian]

Differentiate between key fields and fields that are simply indexed with no
duplicates allowed. You can have multiple fields in a table that are not
primary keys but are Indexed (No duplicates). This allows you as a programmer
to have a unique index (the primary key, perhaps auto-numbered) that can be
completely hidden from the user but also allows you to ensure that there are
no duplicates in one or more of the fields that they see.

You can also use a multi-keyed table, where the combination of two fields is
the primary key (in addition to an auto-numbered indexed field). For example,
if you have a table where a CustomerID will show up more than once, but ONLY
once for each state, then you could have the CustomerID and State fields
together be the primary key but have a separate AutoNumber field that you
treat like a primary key (i.e. in relationships with other tables, etc.).
This would prevent the user from entering a customer twice for the same
state, but allow you to use a single field in your table relationships, etc.

 

[gg]

Brian,

Good ideas. I will incorporate the no duplicates.

Thank you!

gg

 

[John Vinson]

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]

 

[gg]

Hi John,

Thanks for your suggestions.

If I can keep the autonumber for the contract number, would that work with
the character (text) fields for line#+extension? When do I do a conversion
from autonumber to text and how is that done? Could you please explain
step-by-step. I'm new to coding in VB.

This is what I'm now proposing to do..the line item#+extension (i.e. 1-OA,
1-A1, 1-A2,etc.) would all be attached to the same contract number (ie. 27),
the next contract number could also have line item#+extension (i.e. 1-OA,
1-A1, 1-CO1, etc.) and so forth that will all be linked to other tables.

My question now is how do I convert the auto-numeric contract # when
combining into this 3 field index?

Thank you!
gg