Basic Question for Lookups.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Just wanna be sure that after writing so many tables .oof +++ I think I'm
crazy asking this...

IMHO, I'm properly storing Prospect ID pk then FirstName then Middle then
Lastname fields.etc. for 3nf.

But when I'm doing my lookups in a Contract table or meeting table for
example and lookup say a prospect and employee and manger I'm using the
lookup wixard in my tables. When it asks on the fourth screen what to store I
select the actual say Prospect ID not the first or LastName.
But then when I view my records in form view I see only the Ids ? :even
though when in the add mode the lookup up combo box showed the three items ID
First and Last to make intlelligent choice. otherwise with no ID in the drop
downs would not be able to determine which Micheal Jackson to choose.

Problem is my client says he doesn't want to see the IDs only names... Am i
missing somethjing here or have I just not properly explained the concept to
my client..

SO final question is how to show in the view/edit mode after entering the
data all three elements( ID First and Last), Would I have to have three
diffent fields? That would defeat my 3nf foundation wouldn't i, since by
referencing the ID I could query the First and Last etc in queries reports
etc.

I hope this made some sense !

I guess it's late :-)
--
 
First, I would suggest that you don't use lookup fields defined in tables
http://www.mvps.org/access/lookupfields.htm. Use combo boxes on forms. The
combo boxes can be BOUND to the ProspectID but display the combination of
first and last names.

SELECT ProspectID, LastName & ", " & FirstName & " " + Middle
FROM tblProspects
ORDER BY LastName & ", " & FirstName & " " + Middle;

Set the Column Count to 2 and Column Widths to 0";1.5". The Bound Column is
1.
 
I agree with Duane Hookom that it's a good idea to use Forms for data entry.

However, especially in the early phases of setting up your Tables, you
will probably need at times to look at their contents, and if so, you
probably will want to define lookup properties on the foreign keys.

I usually explicitly define a Query similar to the one that Duane showed
you. The Query that I would suggest is similar to what the Lookup
Wizard defines, except that I give mine a name, and I limit it to 2
fields instead of several. The second field presents the data in the
format that I think is going to be easiest to use (concise, unique,
meaningful). If I later discover that it doesn't work well, such as
that it's so concise that the names aren't unique, I can easily change
it by editing the Query.

For example, suppose my Tables look like this:

[Prospect] Table Datasheet View:

Prospect_ID FirstName Middle Lastname
----------- --------- ------ --------
1801550689 Michael Zachary Jackson
2126449339 John J. Smith
675010062 Michael Quincy Jackson

[Contract] Table Datasheet View:

Contract_ID Prospect_ID
----------- -----------
-1839909769 1801550689
855172320 2126449339


For the [Contract].[Prospect_ID] field, I might define a Lookup Query
like this:

[QL_Prospect] SQL:

SELECT Prospect.Prospect_ID,
Left$([FirstName],4) & " " & Left$([Middle],1)
& " " & [LastName] AS Name
FROM Prospect
ORDER BY Prospect.Lastname, Prospect.FirstName,
Prospect.Middle;

Note that these names are sorted properly but that I have truncated them
to keep the field short. If they are so concise that they aren't unique
(e.g., if you have a "Michelle Quaneta Jackson" in your list, too),
you'll need to edit the Query to show enough more of the names to allow
you to distinguish them.


[QL_Prospect] Query Datasheet View:

Prospect_ID Name
----------- --------------
675010062 Mich Q Jackson
1801550689 Mich Z Jackson
2126449339 John J Smith

Back in [Contract]'s Table Design View, I would change what the Lookup
Wizard gave you to refer to the Query [QL_Prospect] instead of the SQL
it places there, and to use a List Box instead of a Combo Box (this is
just a suggestion -- maybe you prefer Combo Boxes). I would set the
properties for [Contract].[Prospect_ID] as follows:

Display Control = List Box
Row Source = QL_Prospect
Column Count = 2
Column Widths = 0;1

Having done so, the Table would now look like this:

[Contract] Table Datasheet View:

Contract_ID Prospect_ID
----------- --------------
-1839909769 Mich Z Jackson
855172320 John J Smith

Its primary key, [Contract_ID], is still not pretty, but there's not
much you can do to improve its appearance. I usually just hide the
primary key via Format --> Hide Columns, making the Table in this case
look like this:

[Contract] Table Datasheet View (much more legible than the first version):

Prospect_ID
--------------
Mich Z Jackson
John J Smith

(You might find reasons to have to examine or manipulate the primary key
field (now hidden), but if so, such reasons might be evidence of a
design flaw in your database. Anyway, if you should need to look at the
raw key value, you can de-hide it via Format --> Unhide Columns.)

As Duane said, it's a good idea to use Forms, but if you need to look at
your Tables via Table Datasheet View or Query Datasheet View, the
foreign keys will be a lot easier to live with if you can pretty them up
via Lookup Queries.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Thanks Duane, You knw I'd that was a perfect answer, short sweet to the point
and even more imporant. Absouletly correct.

Bernard Piette
 
Wow, another very insightful answer from Vincent.

I've found the wizard to be SUPER easey to use and need a rather strong
argument as to why I should stop if at all I should stop, what do you both
think?

So to be sure, is there actually anything technically incorrect by using
lookups, am I breaking some knid of database rule.

Bernard
--



Vincent Johns said:
I agree with Duane Hookom that it's a good idea to use Forms for data entry.

However, especially in the early phases of setting up your Tables, you
will probably need at times to look at their contents, and if so, you
probably will want to define lookup properties on the foreign keys.

I usually explicitly define a Query similar to the one that Duane showed
you. The Query that I would suggest is similar to what the Lookup
Wizard defines, except that I give mine a name, and I limit it to 2
fields instead of several. The second field presents the data in the
format that I think is going to be easiest to use (concise, unique,
meaningful). If I later discover that it doesn't work well, such as
that it's so concise that the names aren't unique, I can easily change
it by editing the Query.

For example, suppose my Tables look like this:

[Prospect] Table Datasheet View:

Prospect_ID FirstName Middle Lastname
----------- --------- ------ --------
1801550689 Michael Zachary Jackson
2126449339 John J. Smith
675010062 Michael Quincy Jackson

[Contract] Table Datasheet View:

Contract_ID Prospect_ID
----------- -----------
-1839909769 1801550689
855172320 2126449339


For the [Contract].[Prospect_ID] field, I might define a Lookup Query
like this:

[QL_Prospect] SQL:

SELECT Prospect.Prospect_ID,
Left$([FirstName],4) & " " & Left$([Middle],1)
& " " & [LastName] AS Name
FROM Prospect
ORDER BY Prospect.Lastname, Prospect.FirstName,
Prospect.Middle;

Note that these names are sorted properly but that I have truncated them
to keep the field short. If they are so concise that they aren't unique
(e.g., if you have a "Michelle Quaneta Jackson" in your list, too),
you'll need to edit the Query to show enough more of the names to allow
you to distinguish them.


[QL_Prospect] Query Datasheet View:

Prospect_ID Name
----------- --------------
675010062 Mich Q Jackson
1801550689 Mich Z Jackson
2126449339 John J Smith

Back in [Contract]'s Table Design View, I would change what the Lookup
Wizard gave you to refer to the Query [QL_Prospect] instead of the SQL
it places there, and to use a List Box instead of a Combo Box (this is
just a suggestion -- maybe you prefer Combo Boxes). I would set the
properties for [Contract].[Prospect_ID] as follows:

Display Control = List Box
Row Source = QL_Prospect
Column Count = 2
Column Widths = 0;1

Having done so, the Table would now look like this:

[Contract] Table Datasheet View:

Contract_ID Prospect_ID
----------- --------------
-1839909769 Mich Z Jackson
855172320 John J Smith

Its primary key, [Contract_ID], is still not pretty, but there's not
much you can do to improve its appearance. I usually just hide the
primary key via Format --> Hide Columns, making the Table in this case
look like this:

[Contract] Table Datasheet View (much more legible than the first version):

Prospect_ID
--------------
Mich Z Jackson
John J Smith

(You might find reasons to have to examine or manipulate the primary key
field (now hidden), but if so, such reasons might be evidence of a
design flaw in your database. Anyway, if you should need to look at the
raw key value, you can de-hide it via Format --> Unhide Columns.)

As Duane said, it's a good idea to use Forms, but if you need to look at
your Tables via Table Datasheet View or Query Datasheet View, the
foreign keys will be a lot easier to live with if you can pretty them up
via Lookup Queries.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.


Duane said:
First, I would suggest that you don't use lookup fields defined in tables
http://www.mvps.org/access/lookupfields.htm. Use combo boxes on forms. The
combo boxes can be BOUND to the ProspectID but display the combination of
first and last names.

SELECT ProspectID, LastName & ", " & FirstName & " " + Middle
FROM tblProspects
ORDER BY LastName & ", " & FirstName & " " + Middle;

Set the Column Count to 2 and Column Widths to 0";1.5". The Bound Column is
1.
Click to expand...
Click to expand...
 
Bernard said:
Wow, another very insightful answer from Vincent.

I've found the wizard to be SUPER easey to use and need a rather strong
argument as to why I should stop if at all I should stop, what do you both
think?

So to be sure, is there actually anything technically incorrect by using
lookups, am I breaking some knid of database rule.

Bernard
Click to expand...

As far as breaking rules is concerned, the link that Duane cited,
http://www.mvps.org/access/lookupfields.htm, lists some reasons not to
use Lookup properties. I personally think these reasons are inadequate,
especially vis-à-vis foreign keys in Tables, and very especially if
those foreign keys have no other purpose than to act as keys (which is
how I usually use them).

You do need to remember that the datum stored in a field with a Lookup
property is NOT what you see in Query Datasheet View or Table Datasheet
View, but for me that's a small price to pay for being able to see
something meaningful there.

Whether you choose to use Lookup properties or not doesn't really have
much effect on the structure or contents of your database; the Lookups
merely affect the appearance. My advice is, if you find them helpful,
use them. Otherwise, get rid of them. Or you could use them for some
foreign keys and not for others.

For anyone else using your database, I suggest that you provide Forms
and Reports that always hide the raw key values (unless the keys are
also employee badge numbers or are otherwise meaningful).

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
From Vincent "My advice is, if you find them helpful, use them." You and
anyone who might want to maintain your application would also need to
understand them which many Access users don't.

It will be interesting to see the amount of traffic generated in these news
groups when Access 12 comes out with multi-select lookup fields in tables
:-( Most of us seasoned old guys (and some younger) are fairly passionate
against lookup fields and other mis-features.
 
Duane said:
It will be interesting to see the amount of traffic generated in these news
groups when Access 12 comes out with multi-select lookup fields in tables
:-( Most of us seasoned old guys (and some younger) are fairly passionate
against lookup fields and other mis-features.
Click to expand...

Woo! "multi-select lookup fields" sounds like fun! (Just kidding -- I
don't know what they're supposed to do, but for me, it's adequate to be
able to define via a Query just enough information about a foreign key
to identify the related record, while hiding the meaningless key itself.
I really don't use Lookup properties for any other purpose, but I use
them heavily for foreign keys.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Actually I think I'm a lookup addict :-) That's why I wanted to sure of the
consequences.. I especialy use them for foreign keys in my primary tables.

Thanks to everyone for all the well thought out answers.
 
Bernard said:
Actually I think I'm a lookup addict :-) That's why I wanted to sure of the
consequences.. I especialy use them for foreign keys in my primary tables.

Thanks to everyone for all the well thought out answers.
Click to expand...

I usually use them when I have a foreign key, but not always. For
example, suppose I have a Table of information on countries, such as DE
for Germany, UK for United Kingdom. In that case, the 2-letter key
value is actually meaningful, so I would just use that value as the key,
and not specify a Lookup property for it where it's a foreign key. But
key values aren't normally as descriptive as that.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Back
Top