lookup question ("lookup column" or "type in the values that I want")

[smile]

lookup question ("lookup column" or "type in the values that I want")

I know lookup is bad in access but I need to choose from 2 scenario:

1.I want the lookup column to look up the values in a table or query
2.I will type in the values that I want.

I need to store some text that is rarely changed or never at all.
I can store it in sepatare table tbl_store

Then I need that data to be available in tables tbl_one and tbl_two

So I create a one table to two relation using look up.

Everything works both ways but if I choose to use "I want the lookup column to look up the values in a table or query" I must select proper relation if I click "plus" sign in table tbl_store. This how access works but what is better sollution from theese 2?

 

[ajetrumpet]

Re: lookup question ("lookup column" or "type in the values that I want")

"I want the lookup column to look up the values in a table or query"

You NEVER want to do this, unless the lookup values are stored in a lookup TABLE. That would mean there is only one column in the table, and the values only sit there for lookup purposes, and the table is never used for display by the database engine itself. The improper practice comes in to play when you have data in a table that is related to a form, or possibly involved with other tables in relationships. If that is the case, you will most likely run into a situation where the data is being pulled twice from two different places at the same time. When that happens, anything is possible, and none of it is good! So in short, use ONE table and ONE column for lookup tables. That is OK.

 

[Rabbie]

Re: lookup question ("lookup column" or "type in the values that I want")

You NEVER want to do this, unless the lookup values are stored in a lookup TABLE. That would mean there is only one column in the table, and the values only sit there for lookup purposes, and the table is never used for display by the database engine itself. The improper practice comes in to play when you have data in a table that is related to a form, or possibly involved with other tables in relationships. If that is the case, you will most likely run into a situation where the data is being pulled twice from two different places at the same time. When that happens, anything is possible, and none of it is good! So in short, use ONE table and ONE column for lookup tables. That is OK.

Adam, he was quoting from the options in the combobox wizard.

 

[ajetrumpet]

Re: lookup question ("lookup column" or "type in the values that I want")

Adam, he was quoting from the options in the combobox wizard.

I know this.

 

[Rabbie]

Re: lookup question ("lookup column" or "type in the values that I want")

You NEVER want to do this, unless the lookup values are stored in a lookup TABLE. That would mean there is only one column in the table, and the values only sit there for lookup purposes, and the table is never used for display by the database engine itself. The improper practice comes in to play when you have data in a table that is related to a form, or possibly involved with other tables in relationships. If that is the case, you will most likely run into a situation where the data is being pulled twice from two different places at the same time. When that happens, anything is possible, and none of it is good! So in short, use ONE table and ONE column for lookup tables. That is OK.

Why never? I find it useful to base lookup/Combos on queries when I only want to use information from a subset of a table. For example I often have a Client lookup on a form header to get data for a particular client and Sometimes I only want to choose between Clients who are owing money. A query make this selection much easier.

If you have a properly normalised database you only store each piece of info in one place so you can't pull it from two places.

With clients I use their last name to select in the box but as lastnames are not necessarily unique I display the first name as well to make selection easier. So multi column displays are useful. Of course what is passed is the ClientID(PK)

 

[gemma-the-husky]

Re: lookup question ("lookup column" or "type in the values that I want")

smile

its not clear what you are really trying to do. can you expand a bit

i think when you say lookups are not recommended, this is a reference to the ability to store the lookup IN the table, so that you have a table of say us states with abbreviations.

in your main table you store the abbreviation.

now if the table field is designed as a lookup you only see the full state name, and not the abbreviation. This is really awkward at times, when sometimes you really want to see how the data is actually stored. Hence in this case, store the state abbreviation as a text field, rather than a lookup - in practice you still use it as a lookup, except its under your control, not done for you by Access.

sorry this is a bit convoluted - i hope it makes sense

 

[smile]

Re: lookup question ("lookup column" or "type in the values that I want")

smile

its not clear what you are really trying to do. can you expand a bit

i think when you say lookups are not recommended, this is a reference to the ability to store the lookup IN the table, so that you have a table of say us states with abbreviations.

in your main table you store the abbreviation.

now if the table field is designed as a lookup you only see the full state name, and not the abbreviation. This is really awkward at times, when sometimes you really want to see how the data is actually stored. Hence in this case, store the state abbreviation as a text field, rather than a lookup - in practice you still use it as a lookup, except its under your control, not done for you by Access.

sorry this is a bit convoluted - i hope it makes sense

Well I have a table where information for lookup purposes will be stores it had PK autonumber and 2 fields one for lookup code, another for description

tbl_opkodas
PK ID
opkodas text
aprasymas text

Then I have other 2 tables that I need to fill with information

tbl_pagrindinis
fk p_opkodas - joined with tbl_opkodas PK ID

tbl_prekiu_db
fk s_opkodas - joined with tbl_opkodas PK ID

Now I can fill the data in those two tables and it is stored in one table only.
The table tbl_pagrindinis works like transaction table when I sell and item that is in tbl_prekiu_db I make an entry there. When I buy items for sale I make entry to tbl_prekiu_db.

In other words tbl_pagrindinis stores information about income - sales from entries in tbl_prekiu_db
tbl_prekiu_db stores information about purchased intems for sale, expenses like tax etc.

I need to identify each record with code to be able to calculate expenses and income by each group the code belongs to like, taxes, land, sales items, sales services, purchased services, purchased items etc.

 

[smile]

Re: lookup question ("lookup column" or "type in the values that I want")

here I created quick example: lookup_problem.zip
See the table tbl_opkodas with two joined to two tables. I would like to have only one but it seems impossible.

I know about table level lookup problem but my real dabase works just fine I don't wan't to brake it

Thanks for your time.

 

[smile]

Re: lookup question ("lookup column" or "type in the values that I want")

help anyone ?

 

[georgedwilkinson]

Re: lookup question ("lookup column" or "type in the values that I want")

Sorry, I, like those before me, can't figure it out.

What specifically is the problem? Why do you think you can't do lookups? And since English is obviously not your first language, please over-explain yourself to us 'cause I don't get it.

 

[smile]

Re: lookup question ("lookup column" or "type in the values that I want")

Sorry, I, like those before me, can't figure it out.

What specifically is the problem? Why do you think you can't do lookups? And since English is obviously not your first language, please over-explain yourself to us 'cause I don't get it.

Yes you are correct English is not my native language.
Sorry if don't understand my problem. I'll try to explain:

I've been using lookups like in my attachment, one table with lookup data connected to another that uses the data. Now I’m in situation where I need to connect the lookup table with not one but 2 tables and in the future I think I will have to connect to another 2 tables, that makes 4.

Is this normal, I mean will access work fine as far as reports are concerned?

I always taught that you can connect lookup table with another table and it is wrong to have one table link to 2 or more.

 

[georgedwilkinson]

Re: lookup question ("lookup column" or "type in the values that I want")

OK, I think I get it.

It is OK for a table to be involved in several 1:M relationships on both the 1 side and on the many side. In fact, that is the whole reasoning behind relational database theory.

I noticed that you don't have "Enforce Referential Integrity" set on your relationships. You might want to look into that, too.

 

[The_Doc_Man]

Re: lookup question ("lookup column" or "type in the values that I want")

When you say "you have to use lookup table with 2 tables" are you talking about two sources of lookup?

Let me expound a bit. When you have a lookup, you are (in essence) associating a specific field of your table to a specific field in a different table. There is an implied many-to-one relationship between these fields (whether you declare it or not).

Let's call the table where you store the data the object table. The place that holds the possible values is the lookup table. You are using the field in the object table as a foreign key to the related field in the lookup table, which (for a combo box to work right) must be the primary key of the lookup table.

If you have more than one possible lookup table for the same field in the object table, this doesn't work. If you have a second field in the object table for which there is a second lookup table, this works perfectly well.

If you have the second case, keep on going. If you have the FIRST case, however, the lookup tables are not properly normalized.

I hope I have explained this well enough to make sense. But if I've made an error in my assumptions, please explain what that error might be. We cannot help you if we don't understand the issue. So if I missed something, then my help will be off the mark.

 

[smile]

Re: lookup question ("lookup column" or "type in the values that I want")

In my case I have one lookup table that holds data and many (4) table that sore data you called object. Is this normal. Wont I have problems like data pulled twice in reports, or unable to make the reports etc. etc.

My database is how I would call it lookup based. I use lookup to fill data in many fields. I don't have "Enforce Referential Integrity" because in some cases the object table can have blank lookup fields where I don't enter nay data.

I know this means that my database is not properly normalized I guess but how I can normalize it when each lookup table contains data only a certain paper document would hold and central tables only gather this data together.

 

[The_Doc_Man]

Re: lookup question ("lookup column" or "type in the values that I want")

Having a lot of lookups via combo-boxes or list boxes is a GOOD thing because it introduces conformity to the records. If you give persons finite choices, they are less likely to enter a value spelled incorrectly or out of range for normal cases.

I understand that you don't want to set "Enforce Referential Integrity" between the foreign key (FK) in the object table and the prime key (PK) in the lookup table. Blanks are blanks however they come about. HOWEVER, there is a "purist" line of thinking that if blank responses are common enough, you might wish to have a default value of blank in those fields AND have a row for blank in the lookup table - which translates to "not defined" or "not given" or something like that. In such cases, you COULD "enforce referential integrity" (RI) safely. I offer that as a thought, not to be taken even as strongly as a suggestion. Maybe just call it one way to look at that facet of the problem. If you can set "Enforce RI" then you can guarantee that a valid value exists in that field. And, in fact, if the answer CANNOT be blank, you can enforce that with the field validation criteria and still retain Enforce RI.

Four tables using the same lookup is possible but suspicious.

Let's take a contrived case where it would make sense: Local retail chain of shops for locally-made crafts - you would have

table of crafts-people, with address including postal codes;

table of craft outlets, with address including postal codes;

table of customers, with address including postal codes;

table of commercial shipping offices, with address includig postal codes.

In a case such as this, having a table of valid postal codes and the names associated therewith would make perfect sense because the four entities named have no obvious relationships. You are just looking up a field that these items have in common, yet they are really separate entities.

However, unless it is a validation case such as I have described, having four tables with the same lookup would send up a flare that it is time to verify that the tables aren't closely related to each other. Closely enough to have at least 80% of their fields in common. In such cases, you would be using four tables to store different views of some things that are, if not sibling data, then close cousin data. Close enough that they should be a single table with some extra field acting as a discriminator.

Only you can decide which case you have. Only you can decide whether it is right. All we can do from a distance is look and suggest.

 

[smile]

Re: lookup question ("lookup column" or "type in the values that I want")

Thanks for your replies. I'll keep using the lookup tables

BTW I have another question. Almost all my lookup tables contain date field and with main table are used to generate a report for certain document they are used to create like income expenses etc.

Since almost every accounting document contains date field I was thinking to store date in one location instead of having to enter it over and over again.

I was thinking to create one date lookup table and connect it to all others but is seems it does not work. If I do this I can't do date limiting queries etc. It's like date fields becomes just plain numbers or text.

 

[Rabbie]

Re: lookup question ("lookup column" or "type in the values that I want")

Date/Time data is stored in access as a real number. The integer part is number of days since 31 December 1900 and the decimal part is the time as a fraction of 24 hours so 0.25 = 06:00 am and 0.5 = 12Noon and so on.

So a date field should be sharable quite easily.

 

[The_Doc_Man]

Re: lookup question ("lookup column" or "type in the values that I want")

Again, there is a possible normalization issue. This time, from the other side than my previous comments.

If the item in table A depends on, say, an employee number and a date, then the prime keys for that table should be the employee number and the date. Somehow moving the date to another table and pointing to some master date index is PERHAPS technically within the standards of normalization, but violates at least the spirit of the idea that the fields on which everything else depend in a given table should themselves be part of the table and, taken together, should comprise the prime key.

(Looking at it, that sentence is almost a steamroller...)

Let's take an example. Employee gets a raise in salary. You want to record this info. The new salary for employee 34521 takes effect 12/25/07 (Merry Christmas!). The new salary entry should be in a table showing the new salary, effective date, and employee ID, with the EmpID and EffDate as prime keys - because that's what the salary depends on. Since we are using the Employee ID rather than a name, I suppose there is no big issue if you pointed to some event register and used an eventID instead of the actual date. But this is a case where I would have to question the practicality of the operation. You end up adding a JOIN operation, which is far more expensive than just storing the bloody date as-is.

I suspect (but would have to think about) the implications of having a single date reference point for multiple events. The key-dependency issues of that concept give me considerable pause here. I think that is perhaps not a good idea.

I also think that you could be misled by the implications of using that date lookup idea. "Date" is a natural value that has meaning in almost any context and is well understood. Employee ID is a synthesized or derived value that has meaning only within a limited scope. Using some sort of common date ID pointer adds a level of complexity and a level of artificiality that might not be useful, and in fact might be confusing.

Also, suppose that these multiple things you track don't always occur on the same day. What do you do to link them together in that case? Answer: Don't link by dates. If this is, say, the equivalent of a case-number, link by case-number. If it is a customer number, link by customer number. Etc. I think of date as not being suitable by itself for a prime key for any event that could occur more than once per day. (Might be OK for membership in a compound PK.)

That was kind of rambling, more "brain-dump" than normal for me. I hope it didn't confuse you too much.