Link Values in One table with corresponding ID vlaues in another table

[Prevost]

Hi there. I have a table (Table 1 let's say) which contains a list of numbers that all have unique ID numbers in another table (Table 2). Is there a way to create a column in Table 1 that will link those unique ID numbers from Table 2 to their corresponding values in Table 1?

What happened was I imported a list from Excel and there are frame numbers which are unique (but I still gave them an ID) and I now realize that I have another table which lists all these products, which all contain this frame number. I would like to replace the frame number with the correct ID number.

Thanks in advance!

[JoeM]

You can use a query to link these two tables together.
If you then wish to write a field from one table into the another table, first create the "blank" field in the table you want to write it to, then use an Update Query to update its value form the other table.

See: http://office.microsoft.com/en-us/ac...005188710.aspx

[Prevost]

Thanks! This is a related question and I can start a new thread if necessary (although I don't think this is a hard question). When I create a relationship and the parent is the manufacturer (there is a manufacturer ID and then the manufacturer in a table) and the child is the manufacturerID field of the product in a different table, how do I prevent non-existent manufacturer IDs from being entered in the product table?

I am just using those as examples for clarity (I hope!).

Thanks again.

[JoeM]

When joining two tables in a query, the default relationship is an "Inner Join". This means it only returns records that match/exist in BOTH tables. So any records that exist only in one table and not the other will not be returned by the query, thus they won't be added via an "Update Query" that uses an Inner Join.

[Prevost]

Sorry, that wasn't related to a query. I created a relationship between the list of manufacturers and the manufacturerID for each product. With my current table, I am unable to have the relationship prohibit me from entering in a Manufacturer ID that didn't exist. I created a new Database and created the relationship and it did in fact stop me from entering an invalid value (so the relationship was enforced). So now I want to figure out why my relationship is not enforced on my current database that I am working on.

[JoeM]

Sorry, that wasn't related to a query.

Sorry, I thought you said it was a related question, so I was working off of that premise. Probably should've posted it to a new thread. To be honest, I very seldom create relationships between tables outside of queries, so I am probably not your guy on this one.

I also have a difficult time analyzing high-level generic scenarios without much detail. I am a very visual person and I like to see details, examples, etc so I can see exactly what you are working with and what you are trying to do.