Re: Lookup Evils

Yeah, this particular database is for storing/retreiving/analyzing
groundwater data. I wish there were more examples out there for designing
this kind of database but they are usually more geared toward business use.

Anyway, I tried adding the units table to the relation window again and that
seems to work fine (I can create both relationships and enforce referential
integrity). I just didn't know if there was something "wrong" with adding
the table to the window more than once. As far as the primary key is
concerned, I too have been thinking about whether I want to use the unit for
the primary key. Its an ongoing debate and I'll have to read more to make
that decision. That's for your useful comments/suggestions.

Jessi

"Evi" wrote:

I can't comment on your table structure - I've no experience in this kind of
db but I can see why you wanted all those unit fields now.

Does it tell you why you can't re-inforce ref integ?
If it's because you want to link the table twice, then just add the units
table a second time to the relationship window.

I suspect that it is because you have made the Unit Abbreviation the Primary
Key field of the Units Table but someone has not used your current
convention (eg they may have put a space in front of the l or written cms
instead of cm).
We can spot the faulty entries by using a Find Unmatched Query to find
anything in your main table's units field that isn't in your Units table.
You can then edit those entries by hand

Switch on Name Autocorrect, just for now. (switch it off when you've
finished everything)
I'm not sure that it is a good idea to use the Unit Abbreviation field as
the Primary Key field (someone else can confirm or deny this). Autonumbers
are safer because they can't be edited at all. They look ugly in tables but
the user need never see them. The combo boxes will contain them in their
first column but the wizard will encourage you to hide this so that the user
will only see the second column with the actual letters.
Lets say you add an Autonumber field to your Units table (call it UnitID)

To start with, add a number field to your Main Input table, never mind what
you call it, lets say also UnitID. In the end you can rename it by the same
name as your original field if you have to.

Once you have used your FindUnmatched table to check that both table have
the same data in the MeasurementUnit field then you can put both tables into
a query window and join them by the Measurement unit field (ie not the ID
field)

Check that the Input table still has the same number of records as before
you joined them. Put the letter fields from both tables into your query grid
and check if they have the same letters in them. (a fairly quick glance at
an example of each unit should do the trick)
Remove the other fields. Change the query to an Update Query. Add the UnitId
field from your Main table.
In the Update To line put

tblMeasurementUnits!UnitID

Do the same for the other UnitID type fields in your other tables.

Because the tables are linked, the UnitID field in your main table will now
have the correct ID number so that if it is added to a query with the
UnitAbbreviation field from the Measurements table you will see the correct
measurement.
Change the query back to a Select query and add fields and in the query
window to and check that all the fields in your main table are now filled.
then you can decide if you want to rename your Main Table's UnitID Field to
the name of your letter field.

Evi



"Jessi" <Jessi@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3BF0E553-3C50-48F1-90B3-8702266D2C48@xxxxxxxxxxxxxxxx
Thank you for the reply Evi.

I thought I would provide a more concrete example.

tblWellCasingDetails
StationID (pk)
ComponentID (pk)
ComponentDescription
InnerDiameter
InnerDiameterUnit
TopZoneDepth
BottomZoneDepth
DepthUnit
DepthReference
Comments

tblHorizontalSurveyData
StationID (pk)
SurveyDate
SurveyGeneration (pk)
CurrentSurvey
SurveyCompany
XCoordinate
YCoordinate
CoordinateUnit
CoordinateSystem
HorizontalDatum
Notes

tblMeasurementUnits
Unit (pk)
UnitFullName
MeasurementSystem
MeasurementType

The first two tables above both contain fields which require an input for
the "Unit". In fact, tbl:WellCasingDetails has two fields which require
an
input for the "Unit" (InnerDiameterUnit and DepthUnit). The table
tbl:MeasurementUnits is meant to serve as a look up table for the two
tables
above and several others. I can create a relationship between
tbl:MeasurementUnits and tbl:HorizontalSurveyData and enforce referential
integrity. However, I run into a problem when I try to create a
relationship
from InnerDiameterUnit and from DepthUnit in the tbl:WellCasingDetails to
the
tbl:MeasurementUnits. I can create the relationships, but cannot enforce
referential integrity. Any suggestions?

Jessi



"Evi" wrote:

.. Putting the units in another table is an excellent idea.

It is indeed good design to create a relationship. However, if you add a
field to an existing table, you may need to run a series of Update
Queries
to get you started before you join up the tables and 'Enforce
Referential
Integrity'

You may even need to update some of the records by hand (using your
combo
box in a form of course) before you can join your tables in the
Relationships Window. (I've even had to resort to calling one of the
items
in my 'lookup' table 'Unknown' because the unit that the inputter used
could
have been almost anything. But this just highlighted the necessity for
the
table)

You can add a 'lookup' table more than once to the relationship window
or in
a query grid so it can join to more than one table but I have a sinking
feeling that you may have a design problem when you say that you have
many
tables in which there is a Unit field - but then maybe I'm being
unnecessarily gloomy.

Evi



"Jessi" <Jessi@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BBB53505-7F56-429A-A623-1E9FFDEE0E52@xxxxxxxxxxxxxxxx
My question is related to the whole discussion of "lookup evils".

I now understand the logic of only including the user interface (the
lookup)
in the form rather than directly in the table. If you have many
tables in
which there is a "Unit" field. The unit field stores a measurement
unit
(ft,
cm, m, L, gallon, etc.). As nearly every table in the database
contains a
"Unit" field I wanted to create a lookup table in which to store the
appropriate units so that users are forced to enter the unit in a
standard
form. I see that I can do this by using a combo box at the form
level.
But,
is it necessary or good design to create a relationship between the
table
data is being entered into and the input table? I apologize if this
question
isn't worded very well, I'm still learning the terminology.

Jessi

"BruceM" wrote:

Thanks for pointing that out. I did a little more experimenting,
and
found
no problems in sorting by the lookup field when it uses a value
list. I
expect it would be the same if I used a one-column row source query.
However, if I'm going to construct a combo box anyhow I'll just keep
doing
it on a form. I see no use for a user interface in a table.

"Pat Hartman(MVP)" <patsky@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:u26RjXsTGHA.4080@xxxxxxxxxxxxxxxxxxxxxxx
Everything the referenced link says is correct but there really
are
cases
where the Lookups won't cause any harm. Let me try to separate
the
times
when a lookup causes a problem and when it doesn't.
Lookup not OK:
1. you have a table of values and that table has a primary key
that is
different from the value you want to display. For example, you
have a
table of department names and each department has an ID. Adding a
lookup
at the table level will cause problems with both nested queries
and
VBA
code due to the confusion between the departmentID and the
departmentName.

Lookup OK:
1. You have a table of values and the primary key is the value you
want to
display. An example might be a State code table. This won't work
if
you
want to display the StateName. In that case the key field would
be
different from the display field.
2. You have a small list of values and the value you display is
the
value
you want to save. An example might be Male, Female, Unknown. If
the
value you want to save is a code - M, F, U then you should not use
a
table
level lookup. Keep in mind that combos and listboxes work fine
with
text
values but you need numeric values for option groups so if you
want to
use
an option group on a form, you'll have to work around the
numeric/text
conversion problem with code.

In ALL cases, lookups on Forms do not cause problems. If you are
not
using lookup value tables, you need to be very careful to keep
multiple
instances of combos in sync. You will keep a validation rule at
the
table
level to ensure that bad data cannot be entered and use combos on
forms to
provide pick lists but you will be responsible for making the same
changes
to the table field's validation rule property and all combos based
on
that
list. The next version of Access will take care of this little
problem
of
keeping the value list and the combos in sync but the current and
older
versions do not. That is why most professionals rely on a table.

I happen to use a common table for all my simple lookups. It is
essentially a table within a table. I have forms and reports and
a
table
that I add to all my applications.

"HelenJ" <HelenJ@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6851185B-1E56-4CD5-B70A-05912C0BA400@xxxxxxxxxxxxxxxx
I have been reading all about the evils of lookups in tables,
however
could
someone please explain how you avoid using them - is the answer
to
have
many
tiny tables with potentially only 2 fields (assuming it is
necessary
to
have
a PK in every table) and often only 2 records.

I am just starting the design of a new database and I seem to
have
several
fields which have 2 or sometimes 3 options and it seems a lot to
create a
table for this.

Many thanks

Helen











.

Relevant Pages

  • Re: How Do I make fields not appear on a form or report
    ... Open the Query in design view. ... the text field from the lookup table to include in your report. ... The Access Web resources page: ...
    (microsoft.public.access.forms)
  • Re: Using Lookup fields
    ... Use of the lookup wizard feature at the TABLE DESIGN level ... lookup feature at the TABLE design level is the thing to avoid. ... You are even ok to use the lookup wizard when you build a query (often, ... just try and build a simple report based on that table with a lookup ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Query returns incorrect column...
    ... Your query is behaving pretty much the way we normally design our databases. ... 2nd column instead of the autonumber. ... Alternatives might be to include the lookup table in your query. ...
    (microsoft.public.access.queries)
  • Re: why cant i edit my form?
    ... form design abit. ... state table is just for a lookup and i do have a combobox ... on my form for it (if the user wants to edit it).. ... >for Users and a subform for the rest of the query. ...
    (microsoft.public.access.forms)
  • RE: Multiply qty of filtered records
    ... If you should want to try doing this with a query (which is probably the more ... etc in the bottom part of the design view. ... I do know how to get this information in a Report, ... Dim rs As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)
Loading