Special Lookup Table - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Special Lookup Table

Scott Axton

3 years ago

OK guys thinking out loud and outside the box a bit here.

So my customer table has 3 phone fields.  All have the format saved in the field NOT just the digits.

In one of Richard's videos, I believe the ABCD, he mentions creating a digits only field but mine would require 3.

I'm considering creating a PhoneLookup table containing all 3 field values in one table just containing the ID, CustomerID, and PhoneNumber as a search or lookup table. Indexed no dupes as composite key.
Does that make sense or would it be a total nightmare?

Am I just better off just writing
If HomePhone = "(719) 555-1212" or CellPhone = "(719) 555-1212" or WorkPhone = "(719) 555-1212 then...

I realize the better way would be a relational table but that isn't practical or feasible for this db.  It would create more problems changing things than leaving it as is for the time being.

Would love to hear your ideas, thoughts, and comments.

Matt Hall @Reply

3 years ago

Scott,

If I understand this correctly, I see four options:
1) Leave it as is
2) Scrub data to numbers only / leave in Cust table (Format in the forms)
3) Create the extended customer data table as described (ID, CustID, PH1, PH2, PH3)
4) Create relational table with ID, CustID, PH, PHDesc(HOME, WORK, ETC.)

Benefits:
1) EASY
2) Error elimination
3) ? (I am not seeing the benefit to this additional step.)
4) Normalization (Improvement of the database)

As I recall, Richard teaches that up to three duplicate fields is ok. I would look at steps 1, 2, or 4 based on the benefit vs work involved.

Being an electrician, If I touch any wiring, I must bring it up to code. As a result, I would tend to lean toward option 4 but that is my personal defect. I think there is probably no wrong answer here.

-Matt

Scott Axton OP @Reply

3 years ago

Matt -
I think you are responding to my post Special Lookup Table.

If so, I appreciate it! I would love to throw out the baby with the bath water so to speak and start fresh. At this time I have to pick and choose my battles and do what I can. Sometimes you just have to put band aids on things because rewrites just break something else.

I'm leaning toward option 4 in the first group right now to test the waters.

Richard or Alex can you combine this post into my original?
And... any input you might offer would be appreciated!

	Adam Schwanz @Reply 3 years ago
If I'm understanding right as far as searching goes, you could get the same functionality (at least to play with) easily with a union query. You could take that further and then format the field with the numbers at the same time however you like, either on the field itself or on an extra field. Just have ID, CustomerID, PhoneNumber, FormattedPhoneNumber

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View