Access: Will lookups in tables cause issues?

[cmo187265]

As a point of reference I have a few posts on this site for a baseball card database I am trying to create (very slowly).

Reference:
https://bytes.com/topic/access/answe...-normalization

Summary:
In the linked post above I have a Join Table I have created so that I am able to further normalize and "link" all of the tables to accommodate 1 card with multiple players, 1 player with multiple cards, 1 player with multiple teams, 1 card with multiple players and multiple teams, etc through these subdatasheets Access automatically inserted.

Problem:
I have read many posts that claim lookup fields are evil but I don't know how else normalize data further in Access.

Take a more simple example with just two tables--
When I create a [tblTransactions] and find that a (fieldCustomers) needs to be broken out into it's own table to further normalize the data I would create a 2nd table [tblCustomers] and then have the (fieldCustomers) in the [tblTransactions] lookup the field the the 2nd table [tblCustomers] to create the relationship.

Question:
How should I go about properly creating relationships in Access when I normalize tables given my problem or confusion stated directly above?

As always, I appreciate the many people on this forum who are so helpful with their replies despite my painful lack of knowledge and inability to grasp this stuff more quickly.

[cactusdata]

I have read many posts that claim lookup fields are evil but I don't know how else normalize data further in Access.

This is not about normalisation.

Lookup fields are evil because they trick you to transform tables to pseudo queries. They may seem "smart" but will leave you nothing but troubles.
So, leave the tables clean, and use queries when you need to display related records.

[cmo187265]

Thank you cactusdata. So how to I join related tables if I cannot lookup other tables? For example my screen shot showed two tables joined by the CustomerID existing in both tables and joining from a lookup.

I did break Customer out into it's own table for the sake of normalization....

Thanks again for your help.

[cactusdata]

Create a query when you need to display related records.

[cmo187265]

Thanks again Cactus. Maybe my question isn't clear. I understand conceptually that if I want to display some data I will need to query that data.

My question is about linking tables (relationships). So I will re-create the steps.

1. In relationship view I show 2 tables, say tblCustomers and tbl Transactions.
2. In relationship view I drag the PK of tblCustomers to a FK in tblTransactions to create a relationship.
3. I open design view of tblTransactions and look at field properties of the FK.
4. When I look at field properties, now under the lookup tab it looks like Access has added a textbox to look up (as a result of my creating the relationship).

Is this an incorrect way to relate the tables? Are you suggested I leave the tables unrelated completely?

Thanks for your help. Again I am very slow to grasp this stuff.

[cactusdata]

I always use the Relations window for that.

Go to menu Tools, Relations and select the tables you wish to create relations for. Then, create the relation(s) you need.

This will give you a visual display of your tables and relations.

[cmo187265]

Thank you Cactus. That is what I did. Are you able to see the images I have been attaching?

What i refer to as relationship view, you refer to as relationship window, so I apologize if I used the wrong term there.

The central point of my confusion remains then. When I do exactly as you have instructed in your last post and as I have been doing all along anyway (creating relationships with the relationship window), Access DOES create a lookup table to the PK of the relationship.

That's really the whole question. Is this OK since "lookups are evil"?

So:
Action -- Create a relationship in relationship window by dragging the PK of one table to the related FK of another table.
Result -- A relationship is created by Access BY making the field in a table (FK) that looks up data (PK) of another table (a Lookup Field).

Thanks again for your help. Look forward to your reply and any other comments that might be out there....

[cactusdata]

I wasn't aware of that.

You can open the table the table in design view, go the field and tab Lookup. Here you can change it to: Textbox.

That will make the table show the real values for that field.

[cmo187265]

Yea...I can choose from Combo, List, or Tex Box but it's all under the Lookup tab so is it still OK?

Doesn't the fact that the Lookup tab is populated with info now under Display Control, even if it is as a Text Box, make this a lookup field?

[NeoPa]

No. Every Field has a Lookup tab that's populated. It can't not.

Only if it's set as ComboBox or ListBox can it behave as a Lookup Field.

[cmo187265]

Thank you Neopa.

After your post, I went back and noticed only a field set to date/time data type had a lookup tab that wasn't populated.

I will assume going forward after your and Cactusdata's help that as long as my field display controls are set to TextBox they are not lookup fields.

As always, thanks for your patience and help. Good to hear from you.

[NeoPa]

Quite right. I hadn't noticed there are some that don't. All the ones I checked had one and that includes Booleans, Numerics, Text etc.

I'm glad I was able to help, even if only a little. CactusData did all the heavy lifting on this one. I will set his first response (Post #2) as Best Answer as it best answers the original question. The rest of it was also helpful but less directly involved.