- Local time
- Today, 05:40
- Joined
- Feb 19, 2002
- Messages
- 43,196
You won't see the problems until you start writing queries with criteria or VBA. Lookups belong on forms. Putting them on table fields simply masks the true underlying data and that is bad.
Filter a database that contain a multivalued field (1 Viewer)
- Thread starter LeslyP
- Start date
- Local time
- Today, 04:40
- Joined
- Feb 28, 2001
- Messages
- 27,122
jdraw suggested not using a table with a lookup field, but rather doing a more "traditional" lookup. (My words, not his.) You asked what it means.
I don't know how familiar you are with database normalization, but the idea of normalizing is to reduce extra copies of stuff in tables. So to do a lookup from a list of known, approved values, you do not have a lookup field.
Instead, on forms you build a combo box do to a lookup of the key value from the table that contains the translation of that key value into text. So on a bound form, the key gets stored but the combo box can be "persuaded" to show you the translation thereof. There is a combo-box wizard that would build one of these for you on the form.
The other way this might work is to build a formal relationship between the field in the main table that would hold the lookup key and the key field (which must be the prime key) in the table that contains the translations. Then you can EASILY build a query that JOINS the main table with the translation table so that you can see both the key and its meaning in the query. This is what I commonly do for reports where the translation might be long but the key is just an INTEGER or LONG INTEGER.
The difference between a lookup field and a lookup table linked via a relationship is that with the lookup table, if you don't need the extra baggage of the lookup in a particular process, you don't need to carry along the translations. Then you have no extra implied records. That is where you can pick up extra records you weren't expecting.
For instance, let's say you have a lookup field called StatusCode. The code itself is really just a number from 1 to 8, let's say. If you tried to recover records with a specific value for some other field and tried to do a SELECT DISTINCT that recovered the status but didn't have a WHERE sub-clause for a specific status code, you might see as many as 8 records, one for each code, because the right code wasn't specified. This is why many of us avoid lookup fields.
I don't know how familiar you are with database normalization, but the idea of normalizing is to reduce extra copies of stuff in tables. So to do a lookup from a list of known, approved values, you do not have a lookup field.
Instead, on forms you build a combo box do to a lookup of the key value from the table that contains the translation of that key value into text. So on a bound form, the key gets stored but the combo box can be "persuaded" to show you the translation thereof. There is a combo-box wizard that would build one of these for you on the form.
The other way this might work is to build a formal relationship between the field in the main table that would hold the lookup key and the key field (which must be the prime key) in the table that contains the translations. Then you can EASILY build a query that JOINS the main table with the translation table so that you can see both the key and its meaning in the query. This is what I commonly do for reports where the translation might be long but the key is just an INTEGER or LONG INTEGER.
The difference between a lookup field and a lookup table linked via a relationship is that with the lookup table, if you don't need the extra baggage of the lookup in a particular process, you don't need to carry along the translations. Then you have no extra implied records. That is where you can pick up extra records you weren't expecting.
For instance, let's say you have a lookup field called StatusCode. The code itself is really just a number from 1 to 8, let's say. If you tried to recover records with a specific value for some other field and tried to do a SELECT DISTINCT that recovered the status but didn't have a WHERE sub-clause for a specific status code, you might see as many as 8 records, one for each code, because the right code wasn't specified. This is why many of us avoid lookup fields.
Oh thank you The Doc Man !! I didn't know Access was doing this in my back 
I though it was the same as creating myself the relationship. I will change all those lookup things.
So, Access is creating functionalities to help us.... but in the end, it's just crap. Like Multivalued fields xD
I though it was the same as creating myself the relationship. I will change all those lookup things.
So, Access is creating functionalities to help us.... but in the end, it's just crap. Like Multivalued fields xD
- Local time
- Today, 04:40
- Joined
- Feb 28, 2001
- Messages
- 27,122
Sometimes, pure merde. Other times, not really so bad.
For instance, the form wizards are really great in erecting a quick-and-dirty but functional scaffold for subsequent customization. I use them all of the time, including the event wizards.
For instance, the form wizards are really great in erecting a quick-and-dirty but functional scaffold for subsequent customization. I use them all of the time, including the event wizards.
Hello you who is looking for a solution !
you can find it here : https://access-programmers.co.uk/forums/showthread.php?t=299509
you can find it here : https://access-programmers.co.uk/forums/showthread.php?t=299509
