Lookup Tables and One-Use Entries

[KenAl]

Hi everyone,

I'm designing a new table for a data entry application and I ran
into this problem.

Suppose I have a table for payment options with 2 columns,
PaymentID and Description.

PaymentID Description
1 Cash
2 Visa
3 American Express
4 Mastercard

Most (90%) of the time, the customer will choose one
of these options. But sometimes, they may have something
"new" and they will enter a new description, say Debit Card.
Whatever the customer entered, I need to store it. Let's
say I have a table for Order and it needs to refer to the
payment option.

My dilemma is, these "new" entries are very likely going to
be one-time use only. Should I store them in the main lookup
table, and clutter it with these "one-timers"?

Is there a better way to design the tables to allow for
one-timers, in addition to lookup table for frequently
used values? Thank you for your help.

 

[tina]

well, personally, i rarely allow users (whether the paying public, or
trained employees) to add values to a combo box droplist at all, certainly
not to limited lists like a payment method. if you could count on everyone
being careful and attentive, it'd work out okay - but, in practice, what you
end up with are a bunch of mis-spelled, essentially bogus entries that
requires that your data be "rehabilitated" periodically - especially before
you can extract meaningful statistcal information based on that field in the
data table.

if the process you're supporting truly accepts "anything" as a method of
payment, then i suggest using a closed list with the most common ones (or
the ones that are important to the company) identified by name. then add an
another record to the lookup table, called "Other". that way the user has a
viable option to choose for those odd or one-time situations.

hth

 

[KenAl]

well, personally, i rarely allow users (whether the paying public, or
trained employees) to add values to a combo box droplist at all, certainly
not to limited lists like a payment method. if you could count on everyone
being careful and attentive, it'd work out okay - but, in practice, what you
end up with are a bunch of mis-spelled, essentially bogus entries that
requires that your data be "rehabilitated" periodically - especially before
you can extract meaningful statistcal information based on that field in the
data table.

I totally understand this hassles of spelling mistakes or what
not. Unfortunately, I don't have much of a choice. I must follow
the specifications, which allow people to enter "Other'.

if the process you're supporting truly accepts "anything" as a method of
payment, then i suggest using a closed list with the most common ones (or
the ones that are important to the company) identified by name. then add an
another record to the lookup table, called "Other". that way the user has a
viable option to choose for those odd or one-time situations.

If the user chooses "Other", they will enter the method in a text
box. How should I store what's in the text box? I need to somehow
link that back to my Order. Any suggestions?

Peferrably, I can avoid doing: if payment is "Other", then lookup
in another table for what the user entered.

 

[tina]

well, you could always just add a text field to the data table, for those
"other" definitions. granted, it probably won't get much use, but keeping
the field in the same table will meet your requirement of "avoiding looking
in another table" for the entered value. you'll have the overhead of an
additional field in the table, but at least Access doesn't reserve space for
unused fields, so it won't be a waste of space in that way.

hth

 

[Jeff Boyce]

Pardon my intrusion...

If the user can add any new payment method (i.e., "chickens"; "peanuts";
"1b3D"), you will need to, as tina so politely put it, "rehab" your data
periodically. But if the users can come up with a legitimate new payment
method once, I suspect they'll use it more than once.

I vote for the "Other" payment method, plus a new field in your data table,
to hold the description of Other. By tying the Enabled property of this
description control in your data entry form to when the "Other" method is
chosen, you won't get data entry/description unless Other is chosen.

Regards

Jeff Boyce
Microsoft Office/Access MVP