My first db and first question

[Malavia]

Feel free to check out my introduction post in the "Introduce Yourself" forum.
I look forward to learning everything I can about Access. For now suffice it to say I am a newbie.

So my first question:

Where should I put name prefixes (Mr., Mrs etc.)?

My field name is 'Prefix' and if I chose Data Type Look up wizard I can either choose "I want the lookup field to get the values from another table or query." or "I will type in the values that I want."

I figured out how to make both options work, but I'm not sure which one to use.

 

[Isskint]

welcome to the world of access.

Whilst the Lookup data type exists, it should not be used in a table (who knows why MS put it there!).
What you should do is create a seperate table, tblPrefixes? with a PrefixID AutoNumber field and a PrefixTitle text field. You enter all the different prefixes in this seperate table.
In your main table, change the Prefix datatype to a number.
Now on any form that has your main table as a recordsource, you place a combobox, and that will do the lookup for you. The combobox wizard will walk you through the steps.

EDIT:
have a look at the attached

 

[way2bord]

Edit: Isskint beat me to the punch -- I've added details to his summary.

The "proper" way to do this is as follows:

3 tables:
Table 1: tblPrefix
P_ID / Prefx
1 / Mr.
2 / Mrs.
3 / Dr.
etc.

Table 2: myNames
N_ID / Name
1 / John Smith
2 / Betty Sue
3 / Joe Bob
etc.

Table 3: Prefix-NameJoin
P_ID / N_ID
1 / 1
2 / 2
1 / 3

Shorthand - you could merge all 3 tables into a single table:

Merged Table: myNames
Prefix / Name
Mr. / John Smith
Mrs. / Betty Sue
Mr. / Joe Bob

OR 2 tables: 1 for prefix, 1 for Names.

My field name is 'Prefix' and if I chose Data Type Look up wizard I can either choose "I want the lookup field to get the values from another table or query." or "I will type in the values that I want."

Typically you would not want to lookup these values.

 

[Malavia]

Thanks for the quick replies. I really like the lookup wizard, but will take your word for it when you say "don't use it".

The other information is good and I will get right on it.

Oh and thanks for the attachment to show me how. Incredibly helpful.

 

[gemma-the-husky]

couple of things

1. choosing between getting the lookup values from another table, or from a value list. If the value list is permanently fixed (eg just Male/Female) then a value list is OK. If it may change, then use a table, as then you (and more importantly any other users) can change the values just by editing the table, rather than having to change the application. Even if you decide to use a value list - you ought to be consistent throughout your app. you wouldn't want to see, say Male/Female in some cases, and Female/Male in others.

Whether you decide to use a value list or a table, you ought to consider adding a "key" (normally) numeric column to the table, and the value list.If you just store the text descriptions in your lookup table you may end up with say

Mr
Mrs
Doctor

if you then decide in the future to change these to

Mr.
Mrs.
Dr.

you will probably need to amend many of the descriptions stored in your database to avoid ambiguities.You can use automatic "cascading" updates, but I prefer not to have ot do this, personally.

if instead, your lookups are

1 Mr.
2 Mrs.
3 Dr.

and your database stores the numeric value, rather than the assocaited text, then you can change the description without any issues.




2. it isn't the end of the world if you define the field as a lookup field. In general though, it is not recommended, and as you get used to access development you will no doubt begin to see some of the drawbacks of using them.

 

[Malavia]

Thanks Gemma for the description of value list v/s another table. That helped me understand the problematic nature of a value list.

In a semi related question, I know it's a good idea to have a separate table for mailing address, but what about emails and phone numbers? Should I include them in the address_tbl, leave them in the people_tbl, or should I have one separate table for profile information that includes the various emails and phone numbers?

I was leaning towards a separate table for each category of information, but than I found information that said that was too much separation, so I got confused.

*I suspect all of my "people" will have two emails and two phone numbers to track.

 

[Isskint]

*I suspect all of my "people" will have two emails and two phone numbers to track.

Too much separation is a possibilty, but given that people can have multiple points of contact, i would always err on the side of a dedicated CommsTable (plus a sub table for types of comm?)