Table Design - Lookup Benefits

[ponderdj]

Hello,

I recently have decided to further normailize an already functioning database to improve efficiency. I wanted to know what the benefits are of using the lookup tab for each column in the table design, beyond just limiting the values that can be entered.

As an example...

tblNames
ID | Name
-----------------------
1 |Lloyd
2 |Harry
3 |Moe

tblJobs
ID | Job
------------------------
1 |Dig
2 |Hammer
3 |Sand

tblBilling
Job# | NameID | JobID | Price/hr
------------------------
1 |3 |2 | $10.50
2 |1 |1 | $12.75
3 |2 |3 | $19.25
4 |3 |3 | $18.80

If I were to use the lookup tab to join tblBilling.Name to tblName.ID, with a combobox, could I have the value of the tblBilling.Name show 'Lloyd' for Job# 1 ? without having to click on the combo box and scroll over to column 2? If I could have it show this, could I then query tblBilling for all jobs Lloyd performs by using 'Lloyd' for the criteria without including tblNames in the query?

-or-

Is this impossible and I should just join the two tables on ID and then query the name? And if so, what is the benefit of that lookup tab then?

Thanks,
David

 

[PHV]

Have a look here (for the benefit issue):

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ponderdj]

PHV,

I'm familiar with the benefits of normalization, but am I wrong by saying you can normalize your DB in access without using the lookup tab in table design? If not, what extra benefits does the lookup tab provide?

 

[PHV]

OOps, look here:

http://www.mvps.org/access/lookupfields.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ponderdj]

No wonder I was having so many problems with it. Thanks, PHV.

 

[r937]

yeah, i'm sorry too

it's a great article (i didn't write it, i asked the author if i could put it up on my site, there are other versions floating around the web, but they're word docs, and i hate word docs on the web)

hey PHV, do you have a web site? you should definitely consider having one, you've probably got a tonne of things that would make great articles, based on how many tough questions i've seen you answer...

r937.com | rudy.ca

 

[PHV]

Hi Rudy.
No, I don't have a web site.
Furthermore, as stated in my member's profile, I'm not an access nor windows guy at all ...

 

[r937]

I'm not an access nor windows guy at all ...

that's probably a good thing

r937.com | rudy.ca