Re: Basic Question for Lookups.

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 Data*** View:

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

[Contract] Table Data*** 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 Data*** 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 Data*** 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 Data*** 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 Data*** View or Query Data*** View, the foreign keys will be a lot easier to live with if you can pretty them up via Lookup Queries.

  -- Vincent Johns <vjohns@xxxxxxxxxxxxxxxxxx>
  Please feel free to quote anything I say here.


Duane Hookom wrote:

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.

Bernard Piette wrote:
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 :-) 

.

Loading