If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Basic Question for Lookups.
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 :-) -- |
#2
|
|||
|
|||
Basic Question for Lookups.
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. -- Duane Hookom MS Access MVP -- "Bernard Piette" wrote in message ... 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 :-) -- |
#3
|
|||
|
|||
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 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 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 :-) |
#4
|
|||
|
|||
Basic Question for Lookups.
Thanks Duane, You knw I'd that was a perfect answer, short sweet to the point
and even more imporant. Absouletly correct. Bernard Piette -- "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. -- Duane Hookom MS Access MVP -- "Bernard Piette" wrote in message ... 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 :-) -- |
#5
|
|||
|
|||
Basic Question for Lookups.
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" wrote: 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 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 :-) |
#6
|
|||
|
|||
Basic Question for Lookups.
Bernard Piette wrote:
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 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 Please feel free to quote anything I say here. |
#7
|
|||
|
|||
Basic Question for Lookups.
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 Hookom MS Access MVP "Vincent Johns" wrote in message m... Bernard Piette wrote: 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 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 Please feel free to quote anything I say here. |
#8
|
|||
|
|||
Basic Question for Lookups.
Duane Hookom wrote:
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. 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 Please feel free to quote anything I say here. |
#9
|
|||
|
|||
Basic Question for Lookups.
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. -- "Vincent Johns" wrote: Duane Hookom wrote: 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. 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 Please feel free to quote anything I say here. |
#10
|
|||
|
|||
Basic Question for Lookups.
Bernard Piette wrote:
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. 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 Please feel free to quote anything I say here. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Basic Pivot Table Question (using 97) | Adam Kroger | General Discussion | 3 | November 25th, 2005 11:33 PM |
Basic question | hello | Running & Setting Up Queries | 1 | November 19th, 2005 12:32 PM |
Combo boxes and drop down lists- basic question | Erica Lais | New Users | 3 | October 18th, 2005 05:53 PM |
Basic question about sending an e-mail in HTML | Lisa | General Discussion | 1 | June 4th, 2005 12:47 PM |
pp template, a basic question | mintspres | Powerpoint | 6 | May 18th, 2005 02:12 AM |