Lookup table confusion (1 Viewer)

[foshizzle]

I've heard and read over and over lookups are bad on tables. I'm still confused on the appropriate usage.

I've attached a sample db here of my current db with 4 tables.
tblAirlineTransactions - contains lookups for the three tables below.
tblAirlines
tblFuelProviders
tblIssueTypes

I've also included a sample form with another lookup. My reasoning is, when users enter any data, I like to verify they are only able to choose any Airline, FuelProvider, etc that has an Active status.

If this is not the correct way, can someone please elaborate or change my sample so I can understand?
Thanks!

 

[theDBguy]

Hi. The main thing to remember is that data entry and interaction should all be done through forms. So, if you don't open the tables, you wouldn't have any confusion. All I did was remove all the lookup fields in the tables, but did not touch the form. See if the form still works.

I however, deleted one field from your table, because I think it was redundant.

 

[plog]

First tblAirlines isn't a Lookup table, it absolutely should exist as is. A lookup table basically holds just 1 real field of data (autonumbers don't count) and is just primarily used to feed fields in another table. Using that definiton, I wouldn't count tblFuelProviders or tblIssueTypes as lookup tables because they have 2 real fields of data (name and status).

However, they have essentially the same structure, which means you could put their data into the same table:

tblLookups
LookupID, LookupType, LookupValue, LookupStatus
1, Provider, British Petroleum, Active
2, Provider, World Fuelds, Active
...
5, Issues, Fuel On Board, Active
6, Issues, Fuel Received, Active

Then when you wanted just a list of Active Issues or Providers you would make a query, filter on LookupType and LookupStatus. If have other tables that meet that structure you just throw their data in there with the appropriate LookupType and have 1 table instead of however many you have now for all your lookups.

However, however--if you just have those 2 tables with that structure you are fine not making that table I described and just living with tblIssuesTypes and tblFuelProviders.

Lastly, I still think the data in GallonsIssued and GallonsReceived should be 1 field not seperate.

 

[mike60smart]

I've heard and read over and over lookups are bad on tables. I'm still confused on the appropriate usage.

I've attached a sample db here of my current db with 4 tables.
tblAirlineTransactions - contains lookups for the three tables below.
tblAirlines
tblFuelProviders
tblIssueTypes

I've also included a sample form with another lookup. My reasoning is, when users enter any data, I like to verify they are only able to choose any Airline, FuelProvider, etc that has an Active status.

If this is not the correct way, can someone please elaborate or change my sample so I can understand?
Thanks!

Hi
In the attached you will see that I have changed the design of the Form.
It now has a Main Form based on the Airline with a Subform based on the Transactions.
I changed all of the Lookup Fields in your tables to be a Number DataType then on the Forms Combobox's to lookup the values from your specific Lists.
Does this help?

 

[Pat Hartman]

Table level lookups are a crutch and they just get in the way when you use queries and VBA. As theDBguy showed you, they are great on forms which is where they belong.

 

[foshizzle]

Hi. The main thing to remember is that data entry and interaction should all be done through forms. So, if you don't open the tables, you wouldn't have any confusion. All I did was remove all the lookup fields in the tables, but did not touch the form. See if the form still works.

I however, deleted one field from your table, because I think it was redundant.

Thanks dbguy - the form does indeed still work. From what I can tell, you only changed the Display Control on the Lookup tab from Combobox to Text Box. Is this correct?

 

[foshizzle]

Hi
In the attached you will see that I have changed the design of the Form.
It now has a Main Form based on the Airline with a Subform based on the Transactions.
I changed all of the Lookup Fields in your tables to be a Number DataType then on the Forms Combobox's to lookup the values from your specific Lists.
Does this help?

i think so? Should I then be making the forms based on the tables, better than queries?

 

[mike60smart]

i think so? Should I then be making the forms based on the tables, better than queries?

The usual setup would be that the Main Parent Form is based on the Table.

Subforms can be based on the Table or a Query.

A query for the Subform is usually used if there are calculations to be carried out.

 

[theDBguy]

Thanks dbguy - the form does indeed still work. From what I can tell, you only changed the Display Control on the Lookup tab from Combobox to Text Box. Is this correct?

Hi. The default display control for a field is a Textbox. If you don't create a lookup field, that's what you will get. So, to remove the lookup in the field, all you have to do is put it back to Textbox.

 

[Pat Hartman]

The user never sees queries unless you export them for him to Excel or a text file. Therefore, he never "benefits" from the look up aspect. What you haven't seen is that the Lookup obscures the actual data and confuses you as to the data type of the field with which you are working. Just an example of the bad things that happen is if you open the query to view it, you will see the text value rather than the ID of the FK. However, if you export that query expecting the text value to be exported, you will be disappointed to find that Access exports the ID rather than the text value.

I always base my forms on queries. I do that because I rarely use Jet/ACE BE's and opening a form bound to a table in a SQL Server database starts the process of downloading what could be thousands of records. Think of the form like a straw. Since users don't want to see all the records, it is far better to use a query with criteria so that the form opens "empty" and the user can enter his search criteria. For complicated situations, I create search forms. The search forms open a list form if the search will return multiple rows but opens the edit form directly if only one row will be returned.