Any alternative to lookup tables

  • Thread starter Thread starter cjg.groups
  • Start date Start date
C

cjg.groups

While creating my first database, I used lookup tables before I knew
better. However, I can't figure out a way to remove them.

Layout:
static tblCounties - CountyCode, CountyName
static tblMunicipalities - CountyCode, MuniCode, MuniName
data tblOrders - OrderID, Address, City, State, Zip, CountyCode,
MuniCode
tblCounties is related one-to-many to tblMunicipalities on CountyCode.
static tables don't change, data table does change.

The database needs CountyCode, CountyName, MuniCode, and MuniName in
different places. tblOrders stores the two Code fields, but shows
their names (by altering Bound Column, Column Count, and Column
Widths).

On the input form, each combo box shows both Code and Name, storing
only Code. Also, the MuniCode dropdown row source is queries based on
CountyCode.

During output to Word mail merge, both Code and Name fields are used.
The extra INNER JOINs to translate Code to Name are a nuisance, but I
use them.

If I need all the data in these static tables, should I eliminate them?
Are they truly evil "lookup tables"? Or is this how it's done? Thank
you.
 
While creating my first database, I used lookup tables before I knew
better. However, I can't figure out a way to remove them.

Layout:
static tblCounties - CountyCode, CountyName
static tblMunicipalities - CountyCode, MuniCode, MuniName
data tblOrders - OrderID, Address, City, State, Zip, CountyCode,
MuniCode
tblCounties is related one-to-many to tblMunicipalities on CountyCode.
static tables don't change, data table does change.

The database needs CountyCode, CountyName, MuniCode, and MuniName in
different places. tblOrders stores the two Code fields, but shows
their names (by altering Bound Column, Column Count, and Column
Widths).

On the input form, each combo box shows both Code and Name, storing
only Code. Also, the MuniCode dropdown row source is queries based on
CountyCode.

During output to Word mail merge, both Code and Name fields are used.
The extra INNER JOINs to translate Code to Name are a nuisance, but I
use them.

If I need all the data in these static tables, should I eliminate
them? Are they truly evil "lookup tables"? Or is this how it's done?
Thank you.
Click to expand...

Lookup *tables* are fine and are found in most well designed databases. What is
frowned upon is using the feature described as a "lookup field" in the design of
your table.

Your setup sounds exactly correct.
 
Thank you for the clarification. I guess my Orders.MuniCode and
Orders.CountyCode fields are "lookup fields" because they show the name
from the look up table but store the ID (by altering Bound Column,
Column Count, and Column Widths). In my outputs, I use both name and
ID, so it was tough to decide which to store. I chose name because
it's more human friendly. Are these "lookup fields"? I guess I could
easily have the form display the name from the ID instead.
 
If your table design view shows fields that have "Bound Column, Column
Count, and Column Width" then you are using lookup fields in your table.
This is generally deemed undesirable by most veteran developers.

Consider storing the Codes in your table and use combo boxes on your forms
to display/select the names.

--
Duane Hookom
MS Access MVP

Thank you for the clarification. I guess my Orders.MuniCode and
Orders.CountyCode fields are "lookup fields" because they show the name
from the look up table but store the ID (by altering Bound Column,
Column Count, and Column Widths). In my outputs, I use both name and
ID, so it was tough to decide which to store. I chose name because
it's more human friendly. Are these "lookup fields"? I guess I could
easily have the form display the name from the ID instead.
Click to expand...
 
Thank you for the clarification. Removing lookup fields is a much
easier fix than removing lookup tables like I'd previously thought.


Duane said:
If your table design view shows fields that have "Bound Column, Column
Count, and Column Width" then you are using lookup fields in your table.
This is generally deemed undesirable by most veteran developers.

Consider storing the Codes in your table and use combo boxes on your forms
to display/select the names.
Click to expand...
 
Back
Top