Basic design question
- Thread starter Shep
- Start date
DBL
Registered User.
- Local time
- Today, 19:45
- Joined
- Feb 20, 2002
- Messages
- 659
I use them all the time to save on user entry and error. For example, I have a table that is LookupCounties. Each county has a unique autonumber and a text field for the county name. In my address table I have the county field as a numeric field. In my address entry form I have deleted the county field that was created from the numeric field and created a combo box that looks up the county name from the county lookup table. The bound field is the county id which is saved to the county numeric field in the address table but it's the name of the county that shows in the combo box.
I use lookup tables for anything where the user might be able to select from a list of options. I'm a great believer in restricting user input!
I use lookup tables for anything where the user might be able to select from a list of options. I'm a great believer in restricting user input!
Thank you DBL.
There is something I still fail to understand, however.
Why would one use a combobox in conjunction with a lookup field, when a combobox based on a normal field can be configured to acheive the same goal?
That goal being restricting user input. I am a firm believer as well. I am really annoyed by illiterate or careless entry.
It's the u and ur and l8tr m8 generation.
I hope they realize I'd never hire them.
McDonald's might...
There is something I still fail to understand, however.
Why would one use a combobox in conjunction with a lookup field, when a combobox based on a normal field can be configured to acheive the same goal?
That goal being restricting user input. I am a firm believer as well. I am really annoyed by illiterate or careless entry.
It's the u and ur and l8tr m8 generation.
I hope they realize I'd never hire them.
McDonald's might...
Welp if you want to get into the thick of things there is alot that can be debated but here goes. I best describe by example:
Table one
ID (Integer autonumber prob), Country (text)
Table two
..., Address, Country(integer), .....
Country in table two is ofcourse linked to id in table one. now why do this? Welp a text feild in size is 8 bytes just for being a text feild and then 1 byte for each char. However a integer feild is 2 bytes. You can save alot of space by not repeating a text value over and over in your tables. This also saves on speed and data integrity. I hope this answers your questions as to why to do it this way. There are many theories on data modeling and some people believe that it is too little space to worry about. So ofcours you can come to your own conclusion. I personally beleive that this is always a good Idea and seldom a bad one... if that makes any sense at all. Let me explain it from a more broad point .
Table one.
CustomerID, Customer Name, Customer Address
Table two
OrderID, CustomerID, Items, etc....
Now if you sold more then one order to the same customer you would still be only keeping one address instead of retyping it over and over again. Also if the customers address every changed you would only have to retype it in one place. This is alittle more then what your asking but I figured I would follow the logic. I hope this helps, forgive me for being long winded.
C U l8tr (sorry couldn't resist)
Table one
ID (Integer autonumber prob), Country (text)
Table two
..., Address, Country(integer), .....
Country in table two is ofcourse linked to id in table one. now why do this? Welp a text feild in size is 8 bytes just for being a text feild and then 1 byte for each char. However a integer feild is 2 bytes. You can save alot of space by not repeating a text value over and over in your tables. This also saves on speed and data integrity. I hope this answers your questions as to why to do it this way. There are many theories on data modeling and some people believe that it is too little space to worry about. So ofcours you can come to your own conclusion. I personally beleive that this is always a good Idea and seldom a bad one... if that makes any sense at all. Let me explain it from a more broad point .
Table one.
CustomerID, Customer Name, Customer Address
Table two
OrderID, CustomerID, Items, etc....
Now if you sold more then one order to the same customer you would still be only keeping one address instead of retyping it over and over again. Also if the customers address every changed you would only have to retype it in one place. This is alittle more then what your asking but I figured I would follow the logic. I hope this helps, forgive me for being long winded.
C U l8tr (sorry couldn't resist)
I understand your logic clearly FloBob and subscribe to same.
I'm left thinking that I was less than clear with my question.
I am referring to the option in Table Design view that allows one to make a field a 'Lookup' field. It's the second tab, down below, where you'd enter Format, Validation Rule, etc. In DataSheet view in a form this will appear as a ComboBox embedded in the datasheet.
I understand it's use there, but am wondering what other uses there are for such a field.
Thank u for ur gr8 reply.
I'm left thinking that I was less than clear with my question.
I am referring to the option in Table Design view that allows one to make a field a 'Lookup' field. It's the second tab, down below, where you'd enter Format, Validation Rule, etc. In DataSheet view in a form this will appear as a ComboBox embedded in the datasheet.
I understand it's use there, but am wondering what other uses there are for such a field.
Thank u for ur gr8 reply.
Ahh! Sorry, I did misunderstand the question.. I dont think there is any real advantage to using that unless you referenced that particular column on several forms. Might save you a bit of work, but I think that I would do it the combo box way still. So to answer your question, Beats me. t8k 1t easE fre1nd.
I dont like that though... I would prefer a seperate table simply because if I need to make a way for the users to update/add to there options, I can do that. If its never going to change then I can see just assigning it to the combo box.
DBL
Registered User.
- Local time
- Today, 19:45
- Joined
- Feb 20, 2002
- Messages
- 659
You can still do that by leaving the field in the main base table as numeric. Create a combo box in the form using the wizard, look to the lookup table for the data and assign the ID field to the numeric field in the main base table. You can still us the NotInList event to add new options to the lookup table if you want.
Similar threads
