Lookup Fields

[mikel]

I'm confused about lookup fields in tables. I've read on this forum that they are bad and should not be used, but why would microsoft create a wizard to create them if they are that bad. I can understand the arguement against them but I don't know how to design my tables without them.



Let's say I have one table of cars which has different fields. One of the fields is the owner. I have another table called owner that has information on the owners. How do I link these tables so each car entry has one owner? I can use the lookup wizard and have it reference the owners table. Or I can drag the primary key in the owners table into the cars table and create the relationship. Which way is better or, are there other, better ways to link simple tables like this without using lookup fields?

One step further if I wanted to create a form, would it be best to link it to the car table, or a query that combines the tables?

Sorry for the long post, just trying to learn something new.

Mike

[NTC]

I understand what you are saying; I am ok with LookUp fields but only when what is inserted is the actual text from the source table; i.e. the 2 letter initials for each state i.e. NY NJ etc. so whenever I use a lookup field - my source table (which I refer to as static lists) are always single column tables.

The messy thing about LookUps is when the source table has an ID column...and so you think you are entering text - - - and that is what displays - - - but what is actually stored in the table is the ID value. That situation is very messy down the road in making reports/queries and such....but one has to understand that MS is attempting to get newbies into dbs with Access and they figure anyone learned enough to get themselves into trouble can learn their way out of it....

the alternative are combo/lists - so you can see alot of data (multi fields) and then in the AfterUpdate event you need to put the value you want into a textbox control that is perhaps not visible.

[mikel]

I see your point with the ID in the lookup table and not having the actual text in the table.

But, I don't undertstand your last paragraph. Aren't combos/lists controls that go on a form? The tables still need to be linked in order for the lists to get the correct data, right? How do you do this? Can I just drag the primary key from one table to the "state" field in the other table? This creates the 1 to many relationship between the tables. Is there a better way to create this relationship? Or how do you do it?

Thanks,
Mike

[NTC]

yes, combo/list boxes go in forms; all user data input should be via forms - never directly into tables. that is a baseline assumption in Access - and actually all databases.

in forms - when you put a combobox onto the form - the wizard set up will step you thru including a 'where do you want to store this value' option. So in situations where you do not want to deply a loopup field method - you can use this method.