Lookup Tables - how to use without linking in table design

[grapevine]

I am creating a simple database that has only three main tables, but many lookups (countries, regions, component types, personnel names etc)



In the past i have used the lookup feature in the tables, but have read numerous post that I should not use this method but to link to the lookup tables when i create queries or in forms and I would like to get this right so would like some definitive clarification on some points.
My questions are

• Do i just create all the tables as is and not use the lookup up feature in the table design area, thus creating a long list of unrelated tables (at this point)
  
  • All my tables including the lookups include an autonumber field - is this correct?
  
  
• Do i just link in the relationship window just the three main tables?
• Do I only use the lookup tables (and main tables obviously) when i am writing a query?
  
  • Do i need to specify joins in the query window?
  • if so what type of join?
  
  
• Do all my forms/reports have to be linked to a query for this method to work?

Many thanks for you help and advice

[ArviLaanemets]

What are lookup tables

It looks like you have entered some repeating info directly into your tables - is it so? I.e. you enter people forename and lastname (or even worse - fullname) several times directly into one or several your tables?

In Access, a good practice is to keep such info in separate tables (I name them registers), and in other tables refer to it only using ID's. P.e. when you have a table to keep personell appointments tblPersonellAppointments, then it is best to have also tables/registers tblPersonell:PersonID, ForeName, LastName, ... and tblAppointments: AppointmentID, AppointmentName, ... too. Then the tblPresonellAppointments will have the structure PersonAppID, PersonID, AppointmentID, AppFrom, ...

With such structure, in forms you use combos to select person or appointment or whatever. You see the name/appointment/whatever, but in source table is really ID only.
In queries you use joins to link different registers to query's main table, and get names/appointments/whatever returned from joined registers instead of ID's in query's main table.

[grapevine]

Thank you for your reply, however I am not using repeating information, that is why I have the lookup tables (registers) for the repeatable/reusable information. The different product type, personel names etc.
In the past I have connected to this data (tables) using the lookup wizard, but that seems to be deemed bad form now. So i still have the lookup tables or registers if you prefer, I just want to clarify exactly how i use them so I get this database built correctly from the start which is why I would like confirmation on the points I have raised above.
Many thanks

[ArviLaanemets]

OK. The name was confusing a bit. But what you mean by <In the past I have connected to this data (tables) using the lookup wizard>? Do you have fields in your tables where you get some information from these lookup tables using DLookup function? If yes, then this is really not best solution. Don't include any formulas into your tables unless you can't avoid it. Even more - don't include any calculated values without real need.

Formulas, calculations, etc. are usually the part of user interface (forms, reports, etc.).

P.e. with my example tables from previous post, to select person's full name for record in form, you add a combo box with PersonID as ControlSource, "Table/Query" AS RowSourceType and a query 'SELECT PersonID, ForeName & " " & LastName AS [FullName] FROM tblPersonell' as RowSource. The BoundColumn will be 1, ColumnCount will be 2, and ColumnWidths will be "0,2.5 cm".

To get person name into report you use a query in report's RecordSource like
SELECT per.FirstName & " " & per.LastName AS [FullName], ... FROM tblPersonellAppointments perapp, tblPersonell per, ... WHERE per.PresonID = perapp.PersonID ...
, and add a field FullName into your report design.

In both cases, the person name from table tblPresonell is never stored elsewhere as in table tblPersonell (and only it's components are stored there), but is calculated and displayed whenever needed instead.

[orange]

grapevine,
Here is a link to info on database planning and design.
You will learn about relationships by working through some of the tutorials under the practical experience part of the info.

Lookups at the table field level create hidden entities.
Having reference tables --separate tables with unique records - and using the primary key as a linking value is a tried and true method. You explicitly create the table, fields and links --nothing hidden.
There are many examples of reference tables such as ISO CountryCodes, ISO LanguageCodes,
USStates, an EmployeeTable where an Employee may be a worker, a supervisor and may perform some other role(Security Officer, CommitteeMember) etc.

[grapevine]

Thank you very much for both of your answers. I am very familiar with database planning and design, but in the past i have always used lookup tables connected via the lookup wizard. I am now creating a new database and do not want to use this method because of the inherent problems. I have already created all the lookup tables but I want to know the best way to use them if i am not connecting them in the table so i get this right from the start

Do I join them in the relationship window? or
Do i only join the key tables (I have set up primary and foreign keys on the main tables only not the reference tables
Do i then have to link them in the QBE window (if i have not linked them in the relationship window) when creating queries or ignore them and only bring them into the forms and reports as described above
Thank you for the clarification on linking in the forms and reports
Many thanks

[orange]

Perhaps you could tell us a little of your database plans and the business it will support.

Suppose you were an Import/Export company (hypothetical for concept only).

Let's say you have import Products from some Countries and export the Product to other Countries.
In other words a Product has a CountryOfOrigin and CountryOfDestination. Let's use the ISOCountryCode to represent Countries.
So you could have tables:

tblISOCountry 
ISOCountryCode  PK
ISOCountryName
  
tblProduct
ProductID  PK
ProductDesc
OtherProductInfo

The Country table could be a reference lookup table

ProductID  0010
ProductName Pickled Herring

ISOCountryCode

alpha2    DK
alpha3    DNK
num3     208
Countryname Denmark
..
..
alpha2   BG
alpha3   BGR
num3    100
CountryName  Bulgaria

...
        Origins                                                                               Destinations
       tblISOCountry                      tblProduct                                tblISOCountry
               +                                 ProductID   0010
               +-------------------------> CountryOfOrigin 208
                                                CountryOfDestination 100 <---------------+

to represent the import of Pickled Herring from Denmark and export to Bulgaria.
The ISOCountry table is used as a Country reference/lookup table. In a data model the table may be identified several times since it is a "lookup/reference" to a value that has meaning to you and your database. In this example Origin and Destination values come from the same reference table. You could have the Table identified many times if you were identifying different countries/ports or waypoints on a shipping itinerary.

I often build a data model (Erwin/Toad..). It acts as analysis and design document and can be part of the plan for the database. It can be a blueprint for your design. It can be used for training and development. As part of a planning document it is a reference to any who have to work with the database or modify the database or make decisions on whether or not to proceed with the database/application. You can use the relationships window to build the model, but you can build a model with pencil and paper (preferred -since it keeps you from jumping into a solution(physical database) before you have done the analysis and design). Too many people jump into physical database and end up with un-normalized structures and a myriad of issues/problems mostly stemming from an untested model. Relationships can be used to enforce referential integrity (but beware of split databases -relationships in front end don't affect back end). You can create joins in query window.

Good luck with your project.

[ArviLaanemets]

It depends!

Most people here don't agree with me, but I define relationships only, when I want to use referential integrity in simple applications - after I years ago had problems with multi-form application with conflicting relations. Even adding second instance of table into relationships wasn't enough. And as currently I design all my databases with SQL server DB as back-end, non-defined relationships don't affect my applications too much. I can always achieve same functionality with design and VBA.

[grapevine]

Thank you Orange
I have created five tables which hold contract details
Each contract can have many different projects
Each project can have many different job tickets
Each job ticket hold information about a varying number of individual jobs and different information about personnel

I have five tables for this
In the tables there are many fields used for reference such as country region, pipe size etc i have created reference tables for these in the way you have described above and want to still use them in this way. A few of the reference tables will indeed be used for several tables

The last time i created a database i linked to the reference tables using the lookup wizard and then used these fields in queries etc and used queries to then get the secondary fiield showing in the forms or queries as Arvil described about; i no longer want to do this as everywhere I look it says you should not use the lookup wizard. I just want clarification on whether I

• only link the main tables - i am assuming that i do
• When i use the QBE window do i include the reference fields or only when i am creating the form and report and then create the query to join the main table to the reference table
• if i use them in the QBE window do i have to add any links

I know i will probably get there by trial and error, but I just wanted a definitive answer so i did not create any rogue links which might cause problems once I get data in the database. I currently only have the reference table populated.
I hope this helps

[orange]

You have started to lay out "business rules/facts:

I have created five tables which hold contract details
Each contract can have many different projects
Each project can have many different job tickets
Each job ticket hold information about a varying number of individual jobs and different information about personnel

You should extend this list such that it includes statements/assertions about

country region, pipe size etc

See this data model from Barry Williams' site, and the related facts that led to the model.



You may get some ideas from this thread-- it shows a dialog re analysis and approaches.

[grapevine]

Thank you but i am ok with setting up the tables and knowing how to normalise the tables, although I will read all the links as i know you can always learn something new.
Maybe i posted in the wrong part of the forum i dont know, but essentially I do not want to use the lookup wizard to link to link to the reference tables as everyone says you should not do this and i just wanted clarification as to how best to continue not using them.

Thank you all for your time
Many thanks

[orange]

Perhaps my posts are not clear. Create the reference table(s) and use them to value the related fields involved. use them in your relationships, or in joins. There is no need to use the lookup wizard. You relate the tables according to your business facts/rules.
If I'm not addressing your specific point, then please describe again with an example.

If you understand a lookup/reference table, and a join between such a table and a field in your related table, and the definition of fields in tables with explicit data types ( not Lookup) , then I'm missing something in your post????

What people advise that you(often referenced article) do not do is use a lookup at the table field level (using Lookup as field data type).

[grapevine]

Sorry, it is probably me not being clear or missing something very obvious

your final point is what i am trying to avoid
What people advise that you(often referenced article) do not do is use a lookup at the table field level (using Lookup as field data type).

So i think what i want to clarify is a join between such a table and a field in your related table - how best to make the join when i dont use the lookup as a field data type
I know i need to link the main tables in the relationship window, but do I
create the links in the relationship window to the reference tables?
or
do i link when i write the queries?

Sorry to be a numpty, it is just I am trying to do something a different way and missing something very obvious somewhere

[orange]

I don't know how to say this more clearly.

Create the reference table(s) and use them to value the related fields involved. use them in your relationships, or in joins. There is no need to use the lookup wizard. You relate the tables according to your business facts/rules.

Did you not follow my sample in post 7?
You may be over thinking what's involved. It's just another table.

From the perspective of tblParticipatesInHobby, you could consider tblPerson and/or tblHobby as a lookup table(s). You get values from these tables based on looking up the related value from your table.

Using PersonID from tblParticipatesInHobby and looking in tblPerson, I can find the associated PersonFName and/or PersonLName by looking up PersonID in tblPerson. PersonID 1 in each table identifies Jon Smith.

Consider people with hobbies.

tblPerson

PersonId autonumber
PersonFName text
PersonLName text

1, Jon, Smith
2, Kody, Akbear
3, Sheeza, Loozer

and tblHobby

HobbyID autonumber
HobbyName text

1, Fishing
2, Reading
3, Canoeing
4, Traveling


where a person may have 0,1 or many Hobbies.

Relationship (concept only)


tblPerson ----->tblParticipatesInHobby<----tblHobby

tblParticipatesInHobby
PersonID
HobbyID


Jon Smith participates in canoeing and fishing,
Kody Akbear likes to Read

1,1
1,3
2,2

[grapevine]

Thank you for your patience. I have now tested it all out and created a form. The relevant fields i have changed to combo boxes and have linked to the underlying table (select statement)
The date input then appears in a query in the normal way.

I think i was over thinking things.
Thank you for your time