LOOKUP Tables - QUESTION

[cmjones007]

Relative basic user so please bear with me!

I have a table named table_products that I use as a reference within table_records. In table_records I can select the product from the drop down list that I created by pointing the field to the separate table_products.

First question is, if I have a new product, have I first got to update the table_products, or can I do something to update table_products automatically by keying the product name in at the bottom of the list?

[orange]

Do not use lookup at the table field level. Build a separate reference table. Use the traditional, universal approach. Lookup within Access table is an Access-only "feature" which most developers will tell you to avoid.
Here is more info.

See this for more description and example.

Good luck

[cmjones007]

Thanks for the reply.

To highlight my incredibly basic understanding, what would a separate reference table consist of? As another example, I have a soccer database of results. table_records would be the table of results, whilst table_opposition would list the team names. The teams would always be static for each records in table_records, and once populated, wouldn't change.

From the link you sent of example, my table_opposition wouldn't change too much, just a few new additions each year.

[orange]

A separate reference table example:

Suppose a hypothetical scenario with People assigned to International Projects. Also consider that the Language of People and primary Language for a Project was important to the business. Also, a Project could involve multiple People. A Person speaks/writes in 1 or Many languages. A Project occurs at 1 or many Locations.

Possible tables:
tblPeople --- people
tblProject --- project
tbl_Junction_PeopleOnProject --people assigned to a specific project - used to remove Many to Many relationships and create 2 1 to Many.
tbl_Junction_PeopleLanguage --languages spoken/written(communicated) by People - used to remove Many to Many relationships and create 2 1 to Many.
tbl_Ref_Location --reference list of locations(Countries)
tbl_Ref_Language -- a reference list of Languages

Possible tables/fields:
tblPeople
PersonID PK
PersonFirstName
PersonLastName
PersonAddrLine1
PersonAddrLine2
PersonAddrCity
other Person info

sample data:

01 John Doe....

tblProject
ProjectId PK
ProjectName
ProjectDescription
PrimaryLanguage fk to tbl_Ref_Language
PrimaryLocation fk to tbl_Ref_Location
other Project info

sample data:
01 Environmental Impact...... eng <----PrimaryLanguage 04 (Bolivia)<--PrimaryLocation

tbl_Junction_PeopleOnProject
PeopleOnProjectID PK
PersonId FK to tblPeople
ProjectID FK to tblProject
other info specific to this person on this project (could have a role[manager, tech support, translator...])

sample data:
01 01(Refers to John Doe) 01 (refers to project 01)

tbl_Junction_PeopleLanguage
PeopleLangID PK
PersonId FK to tblPeople
LangID FK to tbl_Ref_Language

sample data:

01 01 (John Doe) eng (English)
02 01 (John Doe) ara (Arabic)

I'm going to make a list of Countries and Languages for the example. However, there are several "authoritative" lists available set up and maintained by Standards organizations (ISO, UN...). You can take advantage of these if you seek them out and determine their maintenance processes. Be cautious when creating your own reference tables --make sure they have sufficient scope for your application. If you use standards' organizations table, you must keep your usage in sync with their maintenance schedule/scheme.
Please see ISO 639-2 Names of Languages and ISO-3166 Countries for more details. Here is a link to UN Region Codes.
You can use your own PK or you can use the Id of the authoritative table within your tables. If you use your own PK, you should keep the ID provided as a reference number (suggestion)

tbl_Ref_Location
LocationID PK
LocationName

sample data: (my numbering scheme so my maintenance issue)
01 Algeria
02 Argentina
03 Australia
04 Bolivia
05 Canada
06 Camaroon

tbl_Ref_Language
LangID
LanguageName

sample data: (using 639-2 coding)
afr Afrikaans
ara Arabic
ben Bengali
eng English
fre French

you may need to record the name in the Language(s) you use in your application
eg if you work in Swedish, English and German you may want the name of the Language being referenced available in Swedish, English and German. So you could have (potentially)
LanguageNameSwedish
LanguageNameEnglish
LanguageNameGerman (see the table in reference to 639-2 above)

Reference tables (code table/lookup table-- other names for this) is simply an "authoritative" list.These are used to reduce spelling variations/typos, and to reduce the number of potential "individually created tables representing the same concept".(reduces maintenance and improves system/application interoperability).

Here is a data model re soccer and fixtures that may be relevant to your soccer database (but maybe not).