Table relationships question

[hunterfan48]

I've got this table with two particular fields in there (Year) and (Manufacturer).

I've just been having these two fields use a value list to select from for entering the correct value for (Year) and (Manufacturer).

However, I would like to set two new tables up for these two fields that way I have an unlimited amount of records I can enter.

So, I set each table up as follows.

(PK) ID--Autonumber
Manufacturer--Text

(PK) ID--Autonumber
Year--Text

I tried to tie these into my original (Inventory) table that contains these two fields.

How can I relate them so I can

a) enforce referential integrity

and

b) cascade update related fields.

??

It says the relationship type is Indeterminate.

When I try to create, it says no unique index found for the referenced field of the primary table.

Thanks

 

[hunterfan48]

Stupid question...I might as well delete the thread. I just need to go into my original table and set that specific field to lookup the new table and not the value list anymore.

Sorry...

 

[Lightwave]

Remember Hunter it's considered bad practice to do lookups in tables.. (Even though Access allows you to do it)
(I am always being told this but I've actually never had a problem with them)

All you need to do is when you are laying out the form do a combo box and reference the combo box to the new tables created and store the resulting choice in the original table. (The wizard in 03 takes you through the steps)

I must admit I usually just use value lists for years. But manufacturers I'd defintiely link that to a table.

 

[hunterfan48]

Right on...thanks for replying.

I think tho with years...I'll run out of values because I could possibly go all the way back to the early 1900's and of course each new year will also probably be added. Thus, at some point I'll run out of room so I'll just do a table.

 

[Lightwave]

You pretty much new the answer all along...

 

[hunterfan48]

LOL yea by the time it took me to write it all out I figured out what I needed to do

 

[ulieq]

lookup in table vs. lookup in form, someone detail the difference if any.

 

[Lightwave]

Not so much a difference as reasons why lookups in tables should be avoided

http://access.mvps.org/access/lookupfields.htm