Confused about lookup in table v form

[jray7000]

All,

Have written a Relational DB, with many tables. I have PK and FK that join these tables 1-M. One table Prism ID contains the PK and each record is a unique piece of Equipment. The related table is Data Entry, records of inspections that are performed on the equipment. I created a lookup using the wizard in the table called Data Entry under the FK. The FK is the lookup which shows the name of the equipment on the form but store the PK for Prism. I have been reading about the evils of lookups and stuff. I will not be using the FK to do anything other than relate the data entry record to the PK table. so when i do a query it looks like so>

PKID________Equipname_________________FK________IN spect Date_______result________________KPdata entry ID


12345_______boiler motor________________12345_____8/20/2010_________no issue______________1
______________________________________12345_____9/01/2010_________bad motor____________2

which i query for Equipname and inspection date and result.
so i get

boiler motor___8/20/2010___no issue
boiler motor___9/01/2010___bad motor

Do i need to change my lookups? I am confused I thought I had it all correct?

[jray7000]

Is this A stupid question? I really am stumped Have read many books and researched all the past posts. Could use some guidance. plz

[NTC]

your post is not clear as to exactly what you ask. But I get the jist, In general, while the LookUp feature as a table/field property is not totally supported by serious db designers - it is a good feature provided by MS intended to ease newer users ability to create more reasonable & commonly required advancements.

the best generic explanation of a good use is the 2 letter state initials (if one is an American). You have a table with those - let's call it the State table. Then over in any table that has the full address you make the State field into a look up field that points to that State table. ... then in any form you make (or in the table directly) the user will get a dropdown(combobx) at that State field allowing them to select the correct 2 letter state initials. Everyone has seen this in action alot. Works fine & is very convenient.

where things generally go off the rails is when that State table (whatever table you are pointing to) has a key field...and then what is put in the address field is not really the text (IL, AL, GA, etc) but instead is the key value. The initiates a complexity & confusion that causes problems down the road because the newer user misunderstands the actual value being held in the address field.

So in sum, if your lookup table (the 2 letter initial State table in this example) is a single column. It works great. If it is multi column - then you need to know what you are doing and be sure you understand which value is actually being 'bound' into the other table.

Hope this helps a little.

[jray7000]

Thats helps a Great Deal. Thank you for your knowledge and for taking the time to answer my question.