UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Problems Creating Lookup Field, Access 2007    
 
   
JenniferMurphy
post Nov 27 2017, 03:41 PM
Post#1



Posts: 97
Joined: 1-May 14
From: Silicon Valley


I'm trying to create a mailing address. The tblFamily table has fields for Name, Street, City, State, and ZIP. I want the State field to be a lookup field using data in tblStates, which has 3 fields: StatePK (primary key), StateName (text, length 35), and StateAbbr (text, length 2).

To set it up, I add the FamilyAddrState field and select "Lookup wizard" in the Data Type field, which starts the wizard. In the first screen, I select "look up values in a table". In the next screen, I select tblStates. In the next screen, I select the fields StateName and StateAbbr. Does it matter the order? I chose StateName first, then StateAbbr.

In the next screen, I go to set the sort order, but I notice that in addition to the two fields I selected in the previous screen, the primary key field, StatePK, is now also selected. Is that right? I tried removing it, but it comes right back. I set the sort order to ascending on StateName.

In the next screen, I see two columns, StateName and StateAbbr, with the correct data. It tells me to adjust the column widths. I scroll down and notice that the StateName column is too narrow for a couple of the states, so I widen it a bit. The "Hide key column" option is checked.

In the final screen, it asks me for the label for the lookup column. It has FamilyAddrState filled in, so I leave it. The "Allow multiple values" option is unchecked.

I click on Finish. It tells me that the table must be saved and asks if I want to save now, I click Yes.

To my surprise, the Data Type for the FamilyAddrState field has been changed to Number (Long Integer). Shouldn't it be Lookup or at least Text, since the lookup table is all text?

I open the Relationships panel and find that there are no relationships. Is that right?

I switch tblFamilies to Datasheet view and try to enter a record. When I get to the FamilyAddrState field, I see a dropdown and if I type a letter ("c") it fills in California. I want to search on California, but to fill in "CA".

What did I do wrong?

--------------------
Using Access 2007 on Windows XP
Go to the top of the page
 
JenniferMurphy
post Nov 27 2017, 03:49 PM
Post#2



Posts: 97
Joined: 1-May 14
From: Silicon Valley


I just discovered that I was wrong about there not being a relationship. Apparently, the Relationships screen does not dynamically update. Incredible! When I closed and reopened it, it showed a relationship between StatePK and FamilyAddrState. Sorry for the misstatement.

--------------------
Using Access 2007 on Windows XP
Go to the top of the page
 
doctor9
post Nov 27 2017, 03:50 PM
Post#3


UtterAccess Editor
Posts: 17,913
Joined: 29-March 05
From: Wisconsin


Jennifer,

> What did I do wrong?

In short, you created a lookup field. smile.gif Here's an excellent article about why you should avoid them.

Stick to the simple design idea of adding a Long Integer field to your table, and name it "lngStateID". In the database Relationships window, join this field to the primary key of your table of states.

That's it.

Your users should never perform data entry directly in the table, so a lookup field won't help you. Instead, use a Combobox control on the data entry form. The combobox is bound to lngStateID, and uses the StatePK as the first column, then either the StateName or StateAbbr field as the second column (or even both, if you like). Set up the column width of the first column to 0 so it's invisible to the user, but the combobox stores the long integer (hence the "LNG" prefix I suggested) primary key value in the table of mailing addresses.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
JenniferMurphy
post Nov 27 2017, 04:15 PM
Post#4



Posts: 97
Joined: 1-May 14
From: Silicon Valley


Aha! So a Lookup field is only for entering data directly into a table. I thought I was creating the basis for a Combobox on a data entry form. I do know to use forms and not to enter data directly into tables. dunce.gif

I'll probably be back soon. The data entry form is my next task.

Thanks, Dennis

--------------------
Using Access 2007 on Windows XP
Go to the top of the page
 
doctor9
post Nov 27 2017, 04:21 PM
Post#5


UtterAccess Editor
Posts: 17,913
Joined: 29-March 05
From: Wisconsin


Jennifer,

while we're talking about field names, you should also change "Name" to something else, as that is a reserved word in Access.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
JenniferMurphy
post Nov 27 2017, 04:44 PM
Post#6



Posts: 97
Joined: 1-May 14
From: Silicon Valley


I never use "Name" by itself. It's always part of a longer name, like StateName or UserName. Any problem with that?

I have tended to eschew the datatype prefixes (lng, etc.) because (a) I like every field in a table to start with the table name, so (b) my field names are already pretty long, and © my databases are not that complex so I usually know the data types.

What's your rationale for the prefixes and what is your list?

Thanks, J

--------------------
Using Access 2007 on Windows XP
Go to the top of the page
 
doctor9
post Nov 27 2017, 04:49 PM
Post#7


UtterAccess Editor
Posts: 17,913
Joined: 29-March 05
From: Wisconsin


Jennifer,

I only pointed it because you said this:

> The tblFamily table has fields for Name, Street, City, State, and ZIP

I use prefixes that are suggested for Hungarian Notation - they tell you what sort of object you're looking at. Like "tbl" for a table.

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
tina t
post Nov 27 2017, 05:40 PM
Post#8



Posts: 5,195
Joined: 11-November 10
From: SoCal, USA


QUOTE
I never use "Name" by itself. It's always part of a longer name, like StateName or UserName. Any problem with that?

no.

QUOTE
I have tended to eschew the datatype prefixes (lng, etc.) because (a) I like every field in a table to start with the table name, so (b) my field names are already pretty long, and © my databases are not that complex so I usually know the data types.

that's not "wrong", but as you say, the fieldnames then become long and awkward. also, when you create a query, Access will automatically include the tablename for each specified field, in the SQL statement - so MyTableNameAndFieldname becomes MyTableName.MyTableNameAndFieldname. again, not "wrong", but makes it longer and harder to read.

QUOTE
What's your rationale for the prefixes and what is your list?

naming conventions are not set in stone; most developers come up with their own "standard" over time. the most important issue in developing a naming convention is to be consistent. if somebody else has to troubleshoot your database, they will quickly be able to see what you've used in that db, and it will be easier for them to identify the objects and work with them.

i use the more-or-less-"usual" prefixes for database objects: tbl for table, qry for query, frm for form, rpt for report, mod for module. for each table i create in a single db, i choose a 3- or 4-character prefix that i add to every fieldname in that table, so i can easily see which table a field belongs to, in a multi-table SQL statement and in VBA code. for instance:

tblEmployees
empID (primary key)
empFirstName
empMI
empLastName
empDOB
etc, etc, etc.

for foreign keys i use the prefix, then an underscore, then the original fieldname, as

tblCourses
crsID (primary key)
crsName
crs_empID (foreign key from tblEmployees; this could be a field to identify the course instructor)

an advantage of this convention is that every field in every table in a database has a unique name - you never have the problem of "which table does this field belong to" in a multi-table query.

that's just one example. again, every developer comes up with his/her own scheme over time, and the most important point is to be consistent in usage, within a single database. also, some "best practice" guidelines are: don't use spaces or special characters (with the exception of the underscore, and use that sparingly) in the name of anything that you name in Access. you can use number characters if you must, but better to avoid using them at the beginning of a name.

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
GroverParkGeorge
post Nov 27 2017, 06:55 PM
Post#9


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


PMFJI:

"Aha! So a Lookup field is only for entering data directly into a table."

Actually Lookup fields in tables are not for data entry. They are only good for creating confusion and causing problems. dazed.gif

The irony is that inexperienced users--who usually get all tangled up trying to implement them--are more likely to fall into their trap than experienced developers who can handle them more effectively, but who avoid them.

--------------------
Go to the top of the page
 
BruceM
post Nov 28 2017, 07:55 AM
Post#10


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


In the article Dennis posted about lookup fields, some of the listed problems are rather minor, but others are quite important. One thing (the second point in the article) of immediate practical note is that sorting on the state name or abbreviation, or using the name or abbreviation as a criterion, will not work because the stored value, which is what is being used for ordering or filtering, is a number.
Go to the top of the page
 
John Vinson
post Nov 29 2017, 05:38 PM
Post#11


UtterAccess VIP
Posts: 4,143
Joined: 6-January 07
From: Parma, Idaho, US


I may be a little bit heretical on this one issue, but I always use a States table with just two fields: StateCode (ID) and StateName (Idaho). A two character text field is just as efficient as a 32 bit Long Integer, even with Unicode characters, it sorts correctly, and it usually makes it unnecessary to add an extra table to the Query for a report, since the two letter abbreviation is sufficient for most uses.

For what it's worth, my States table includes all the Canadian provinces as well.

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 
JenniferMurphy
post Nov 30 2017, 01:58 PM
Post#12



Posts: 97
Joined: 1-May 14
From: Silicon Valley


QUOTE
i use the more-or-less-"usual" prefixes for database objects: tbl for table, qry for query, frm for form, rpt for report, mod for module. for each table i create in a single db, i choose a 3- or 4-character prefix that i add to every fieldname in that table, so i can easily see which table a field belongs to, in a multi-table SQL statement and in VBA code. for instance:

tblEmployees
empID (primary key)
empFirstName
empMI
empLastName
empDOB
etc, etc, etc.

I like that. It allows me to have longer table names without having to worry about even longer field names. Thanks.

One question though: Do you then not use data type prefixes (lng, int)? It seems that it would be a bit more difficult given that you already have a lowercase table prefix. I suppose you could use the underscore convention that you use for foreign keys (dat_empDOB).

QUOTE
for foreign keys i use the prefix, then an underscore, then the original fieldname, as

tblCourses
crsID (primary key)
crsName
crs_empID (foreign key from tblEmployees; this could be a field to identify the course instructor)


I like this, too. Maybe I should hire you to tutor me.


Thanks, tina

--------------------
Using Access 2007 on Windows XP
Go to the top of the page
 
JenniferMurphy
post Nov 30 2017, 02:01 PM
Post#13



Posts: 97
Joined: 1-May 14
From: Silicon Valley


QUOTE
Actually Lookup fields in tables are not for data entry. They are only good for creating confusion and causing problems.

Got it -- and I'm sold.

QUOTE
The irony is that inexperienced users--who usually get all tangled up trying to implement them--are more likely to fall into their trap than experienced developers who can handle them more effectively, but who avoid them.

That certainly fits me. Without this forum to set me straight, I would have wasted a lot of time trying to make the unworkable work.

Thanks to all who contributed... cheers.gif

--------------------
Using Access 2007 on Windows XP
Go to the top of the page
 
tina t
post Dec 1 2017, 08:10 PM
Post#14



Posts: 5,195
Joined: 11-November 10
From: SoCal, USA


QUOTE
One question though: Do you then not use data type prefixes (lng, int)?

no, that's not part of my personal naming convention for table fields. i use data type prefixes when i declare variables, in VBA code. and when i name unbound controls in forms and reports, i'll use prefixes such as txtName, cboCities, lstOrders, but that has more to do with identifying the type of control.

it's a trade-off. using data type prefixes in table fieldnames would probably make it a bit easier for another developer - who is not familiar with the db, and perhaps not the process it supports - but i would lose my "table identifiability (is that a word?)" and lose the guaranteed uniqueness of fieldnames in a specific database. i'm not willing to do that, so it's the choice i made.

again, a big key is to be consistent in the naming convention you use in any given database - that makes it easier on you, and on the next guy that may come along.

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
GroverParkGeorge
post Dec 2 2017, 07:10 AM
Post#15


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


"... would have wasted a lot of time trying to make the unworkable work."

I just want to be clear.

Lookup fields can be made "to work" at the most basic level. It's when you try to push them to do more, or do things in a way that's not consistent with their design or intention, that they go haywire. It's in that sense which I mean experienced developers can manage them because they understand the limitations and wouldn't try to do things that aren't supported.


--------------------
Go to the top of the page
 
GroverParkGeorge
post Dec 2 2017, 07:12 AM
Post#16


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


It's long been my PERSONAL opinion that prefixing datatypes onto field names is an unnecessary and bothersome practice. However, I would not condemn nor endorse their use. It's like other aspects of what we do. As long as you are consistent, it is workable.

--------------------
Go to the top of the page
 
JenniferMurphy
post Dec 2 2017, 11:34 AM
Post#17



Posts: 97
Joined: 1-May 14
From: Silicon Valley


Response to post by Dennis on Nov 27 2017, 01:49 PM:

QUOTE
I use prefixes that are suggested for Hungarian Notation - they tell you what sort of object you're looking at. Like "tbl" for a table.


Thanks for that link. It only appears to refer to objects, not fields. Do you have a link for that? I'm inclined toward tina t's system of using an abbreviation of the table name and eschewing the data type prefixes.

CODE
tblCustomers
</span><span class="postdetails"><span class="postdetails">custNameLast
</span>custNameFirst
custStatus
    ...


Your thoughts?

--------------------
Using Access 2007 on Windows XP
Go to the top of the page
 
JenniferMurphy
post Dec 2 2017, 11:46 AM
Post#18



Posts: 97
Joined: 1-May 14
From: Silicon Valley


Response to post by tina t on Nov 27 2017, 02:40 PM:

QUOTE
i use the more-or-less-"usual" prefixes for database objects: tbl for table, qry for query, frm for form, rpt for report, mod for module. for each table i create in a single db, i choose a 3- or 4-character prefix that i add to every fieldname in that table, so i can easily see which table a field belongs to, in a multi-table SQL statement and in VBA code. for instance:

tblEmployees
empID (primary key)
empFirstName
empMI
empLastName
empDOB
etc, etc, etc.

I like that. Thanks. Much shorter than my method of prefixing the full table name and just as clear.

QUOTE
for foreign keys i use the prefix, then an underscore, then the original fieldname, as

tblCourses
crsID (primary key)
crsName
crs_empID (foreign key from tblEmployees; this could be a field to identify the course instructor)

an advantage of this convention is that every field in every table in a database has a unique name - you never have the problem of "which table does this field belong to" in a multi-table query.

You like "ID" rather than "PK" (crsPK)?

The underscore in the FK name is because you now have 2 prefixes, right (crs & emp)? I suppose that method could be used to have both a table and a data type prefix on each field name: lng_prodWeight.

Thanks

--------------------
Using Access 2007 on Windows XP
Go to the top of the page
 
tina t
post Dec 4 2017, 02:35 PM
Post#19



Posts: 5,195
Joined: 11-November 10
From: SoCal, USA


QUOTE
You like "ID" rather than "PK" (crsPK)?

The underscore in the FK name is because you now have 2 prefixes, right (crs & emp)? I suppose that method could be used to have both a table and a data type prefix on each field name: lng_prodWeight.

if you allow Access to add a primary key field to a table for you, the default name is ID. add a prefix to that, and now it's a unique fieldname. probably that's where i got it from, though after 20 years, i don't really remember why i started using ID for primary key fieldnames. however, my personal best practice is to use ID in every primary key fieldname, and i don't use "ID" in any fieldname except for primary key fields - again, being consistent is my goal.

the format of my foreign key fieldnames is consistent, and unique in my naming convention - i don't use an underscore in any fieldname unless it is a foreign key field. so one glance tells me a) it's a foreign key field, b) what table the foreign key field is in, and c) the exact fieldname of the corresponding primary key field and the table that's in.

remember, there are very few "wrong" naming conventions, keeping in mind the best practices of not using spaces or special characters (other than underscore) in anything that you name in Access. come up with a naming convention that makes sense to you, and you're comfortable with, and use it consistently, and it'll be fine.

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
JenniferMurphy
post Dec 5 2017, 02:54 PM
Post#20



Posts: 97
Joined: 1-May 14
From: Silicon Valley


In a post on 12/0417 at 11:35 AM, tina t said:

QUOTE
if you allow Access to add a primary key field to a table for you, the default name is ID. add a prefix to that, and now it's a unique fieldname. probably that's where i got it from, though after 20 years, i don't really remember why i started using ID for primary key fieldnames. however, my personal best practice is to use ID in every primary key fieldname, and i don't use "ID" in any fieldname except for primary key fields - again, being consistent is my goal.

the format of my foreign key fieldnames is consistent, and unique in my naming convention - i don't use an underscore in any fieldname unless it is a foreign key field. so one glance tells me a) it's a foreign key field, b) what table the foreign key field is in, and c) the exact fieldname of the corresponding primary key field and the table that's in.


Thanks for the comments. You make some very good points. -J

--------------------
Using Access 2007 on Windows XP
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 03:20 AM