By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,730 Members | 1,659 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,730 IT Pros & Developers. It's quick & easy.

Access: Will lookups in tables cause issues?

P: 29
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.
Attached Images
File Type: jpg LookupTable image.jpg (137.7 KB, 19 views)
File Type: png Relationship image.png (28.8 KB, 16 views)
2 Weeks Ago #1

✓ answered by 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.

Share this Question
Share on Google+
11 Replies


P: 65
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.
2 Weeks Ago #2

P: 29
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.
1 Week Ago #3

P: 65
Create a query when you need to display related records.
1 Week Ago #4

P: 29
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.
Attached Images
File Type: png Field Properties.png (22.3 KB, 6 views)
1 Week Ago #5

P: 65
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.
1 Week Ago #6

P: 29
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....
1 Week Ago #7

P: 65
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.
1 Week Ago #8

P: 29
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?
1 Week Ago #9

NeoPa
Expert Mod 15k+
P: 31,606
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.
1 Week Ago #10

P: 29
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.
1 Week Ago #11

NeoPa
Expert Mod 15k+
P: 31,606
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.
1 Week Ago #12

Post your reply

Sign in to post your reply or Sign up for a free account.