Discussion: using combobox & listbox lookups in table field design

[King Ron]

Ola all. In responding to a recent post requesting help with a search
issue, I recommended using a combo box lookup in the table design.
"paii, Ron" (no relation) posted this reply:

" There are many posts in this group explaining the pitfalls of using
the
lookup feature in tables. Best practice appears to be, keep the
lookup in
the forms using combo box or list box."

I will grant that, in a theoretical world, best practice is to handle
data only on the data side, and leave the interface on the GUI side.
Point taken.

However, I think that its possible to distinguish between 'pitfalls'
and 'annoyances', and also necessary to distinguish between database
theory and production application design.

In my experience the only major annoyance with table field lookups is
that it's often confusing for users because a standard search for
'known' displayed values will return no hits. That annoyance is way
offset by the fact that by properly structuring table field lookups,
those lookup features are automatically propagated thru to the form -
in the form designer you can drag and drop the fully designed lookup
control on any number of forms. 'Design once - instance many'

I'm wondering what you all think about this - are there other
'pitfalls' that I'm not aware of? Does the convenience of a design
shortcut offset the stigmata incurred by breaking the taboo of pure
relational design? Any strong opinions? Fill me in...

Ron, King of Chi

[Larry Linson]

"Lookup fields" in Tables are _only_ useful to users who do data entry or
manipulation in Table Datasheet view. If someone else has to go in later to
correct or enhance your Database, they are very likely to cause not just
irritation, but delays -- of course, it is something that can be dealt with,
but delays cost. For me, that would also apply if I have to go back and work
on my own database months later; and I believe that to be true for anyone
who does not a permanent photographic memory.

And, because there are so many _things that can go wrong_, even
unintentionally , when working in Table Datasheet view that I just don't
recommend it, anyway (and the same is true of most experienced Access
users/developers that I know). So, I strongly recommend _against_ using
lookup fields (and subdatasheets and multi-value fields, aka 'complex
data').

As an aside, I'm not privy to the underlying reasons for the Access team's
design decisions, but I know there was / is a Marketing emphasis on
supporting SharePoint data with Office 2007, and without multi-value fields,
Access couldn't / wouldn't fully support SharePoint data.

There are good and practical reasons for "pure relational design"
priciples -- it's not just an academic exercise, if that's why you were
implying.

If you are an end-user not advanced beyond Table Datasheet view, and you are
the only one ever to use your databases, and you are sure to remember that a
particular field is a Lookup Field OR you will never create a Query that
would refer to that Field, then Lookup Fields will likely do you little or
no harm. Otherwise, avoid them so they won't "rise up later and bite you in
the tender places".

Larry Linson
Microsoft Access MVP

[CDMAPoster]

On May 10, 11:51 am, "Larry Linson" <boun...@localh ost.notwrote:

...
As an aside, I'm not privy to the underlying reasons for the Access team's
design decisions, but I know there was / is a Marketing emphasis on
supporting SharePoint data with Office 2007, and without multi-value fields,
Access couldn't / wouldn't fully support SharePoint data.

Larry,

Do you think that the existence of InfoPath within SharePoint affected
any other deprecation decisions such as DAP's?

James A. Fortune
CD********@Fort uneJames.com