Can 1 Table be Looked Up by 2

[RichardAnderson]

I have a question about the best practice with Lookup Fields.



3 Tables... tbl_customers, tbl_jobs, & tbl_states.

In the first 2 tables there is a Lookup Field to select a state. It works fine except for when I open the states table to see how the relationship is working. When I expand a state, Access asks me to pick a related table to show related data. This led me to think that maybe I need 2 tables for states. One to be the lookup table for customers and one for jobs.

OR

Should I even use a table to be the lookup source for states? I mean the list will never change. So maybe when I do the Lookup Wizard I could choose the 2nd option to type in the values of the lookup list.

[isladogs]

You can reuse the same reference table as many times as you like.
If you need to use it more than once in the same query, just add the table more than once as needed

[orange]

Most people will tell you to avoid lookup fields in tables.

Here's a quote from another forum by a very experienced developer in response to a new user building some tables:

You were also lured into the mistake of using a combo box for the leave types in that table. I know, I know, Microsoft makes them appear so COOL! They are not; they are a trap for the inexperienced. We will convert them to a proper Lookup table.

Read this link to see the design and use of a lookup table.

Good luck with your project.

[RichardAnderson]

Read this link to see the design and use of a lookup table.

Good luck with your project.

I read that answer. I take it I should do this???

You can create a "lookup" table for positions like this.
createtable positions (
pos_name varchar(10)primarykey
);

insertinto positions
selectdistinct position
from employees;

altertable employees
addconstraint emp_fk1
foreignkey(position)
references positions (pos_name);


Where and How do I do this? The OP did not say.Do I make a macro or something?

[RichardAnderson]

I tried this but I get an error.

I put that code into a black Query in SQL view and when I clicked run I get a syntax error in CREATE TABLE

[isladogs]

You notice I said reference table e.g. tblStates with fields StateCode; StateName
Definitely no lookup fields i.e. no combo boxes

[orange]

Richard,

You should learn some SQL.
That may not be the best example of creating a lookup table and it does focus on the poster's question.


which starts:

I

'm having trouble figuring out exactly how to place good boundaries for when and where to use lookup tables in a database. Most sources I've looked at say that I can never have too many but, at some point, it seems like the database would be broken down into so many pieces that, while it may be efficient, it is no longer manageable. Here's a thrown together example of what I'm working with:

Let's say I have a table called Employees:

ID LName FName Gender Position
1 Doe John Male Manager
2 Doe Jane Female Sales
3 Smith John Male Sales

Your sql syntax is incorrect. There are many posts and youtubes and articles on SQL.
You can also find posts on lookup tables.

Your sql would look more like this, but you should work with your own design and just use the references to help guide you with concept and syntax.

Sub fromStackOverflow()

'note I did not create the Emplouyees table so can not run all of the code
'I did create the sql and do execute the code to create the Positions table.



Dim sqlC As String: sqlC = "create table positions (" _
& "  pos_name varchar(10) primary key );"

Dim sqlI As String: sqlI = "insert into positions " _
                                   & " select distinct position from employees;"

Dim sqlA As String: sqlA = "alter table employees " _
                                    & " add constraint emp_fk1 Foreign key(Position) " _
                                     & " references positions (pos_name);"

CurrentDb.Execute sqlC, dbFailOnError  'this creates the Positions table
'....
'....
End Sub

Link to detailed lookup table info:
https://www.red-gate.com/simple-talk...tables-in-sql/

In general terms a lookup table is a reference table. Typically it would include a unique identification field and a text field representing the term/value.
Perhaps better to give an example.

Suppose your were dealing with Products and Shipments and Suppliers.

Suppose a Product comes from the CountryOfOrigin, and is shipped by a Supplier whose parent company is registered in a CountryOfRegistration and further suppose the Customer is located in CustomerCountry.

You could have a CountryLookupTable
with CountryAlpha2
or CountryAlpha3
and CountryName text



Sample data from internet (3199-1/2)
ISO Alpha-2, Alpha-3, and Numeric Country Codes

Country or Area Name	ISO "ALPHA-2 Code	ISO Numeric Code UN M49 Numerical Code
Afghanistan	AF	004
Aland Islands	AX	248
Albania	AL	008
Algeria	DZ	012

....


You could have the 1 country Lookup table and use the alpha2 value to identify the CountryOfOrigin
and another value from the lookup table to identify the CountryOfRegistration, and yet another for CustomerCountry.

The lookup table holds values that can be used in different contexts.


Here's another example/article excerpt from this site::

A Classic Lookup Table

Let’s consider a classic role for a lookup table. We know that in various places we will prompt for the state in an address. We don’t want the users to be able to type the state in directly. We want to force the users to select from a list of valid state options. Our first pass at a lookup table may look like the following (Figure 2):


Figure 2. Example of a lookup table specifying the state name and abbreviation for the valid states
We would use the lookup table in our data model, as shown here (Figure 3):


And another link to review:http://www.itprotoday.com/microsoft-...-lookup-tables