Lookup of client names

[Mela]

Hi -

I am aware of the challenges with lookup tables but am trying to find an
easy way to set up a listing of client names so they don't have to be retyped
each time. I am creating a contract signature tracking database and will use
the form to enter the contract details as they are being tracked. I want to
make this log available to our sales force so they can see the status of
their client contract and thus want them to be able to apply filters based on
the client name. I also want to be able to run regular reports based on
client name.

So, what is the best way to set up the client lookup field? Do I create a
lookup table and create a relationship between that table and the table with
the contract details? I don't quite understand the best way to do this and
if lookups are a challenge then what's the point of having a database? Seems
I should just continue to use Excel! Thank you.

Mary

 

[KARL DEWEY]

I am aware of the challenges with lookup tables
I think if you read the post again that most are talking about 'lookup
fields' in a table. That is different from lookup table used to feed a
combo or list box.

 

[Mela]

Thank you. What about entering the actual values in the form combo box? Or
is that not a good idea?

 

[KARL DEWEY]

Some folks use a sinble column combo and instead of storing a reference
number that equates to the names in a number field they store the actual name
in a text field.

One advantage doing it this way is you do not need to join the lookup table
in your queries to display the name.

 

[Dale Fye]

Generally, I use two columns for my combo boxes, the ID value which is the PK
in the "lookup" table you are pulling data from, and then the data I actually
show in the combo. The combo gets properties:
Column Count: 2
Bound Column: 1
Column Widths: 0, 3 or something like that

What then goes in the field of your application that the combo box is bound
to is the ID value. As Karl mentioned, this causes you to have to add the
"lookup" table to your queries when you want to get the "Name" associated
with that ID value. The thing you need to be careful of is that when you
create these queries, you make them (Left or Right joins) so that they
include all values from your main table and only those values from the
"lookup" table that match. This allows you to see all the records in the
main table, even if someone has failed to make a selection from the combo box.

 

[Mela]

Thank you for the tips. I think I'm having issues with trying to create
joins that maybe I don't need to create.

 

[Larry Linson]

Thank you. What about entering the actual values
in the form combo box? Or is that not a good idea?

If you are willing to code data into the structure of your database
application, that's OK. I try to avoid such, and keep all my data in
tables, which I store separately from the user interface, queries, forms,
reports, macros, and modules. Having data hard coded in a Control can be
confusing, even to yourself, when you go back later to enhance or maintain
the database application.

Larry Linson
Microsoft Office Access MVP