Hi, I'm new to access 2007 and having problems with creating relationships between multiple tables. Here are the tables in a simplified form:

MITMAS - 400 items
Item No. (primary key)
Name

MITLOC - 300 items
UNID (primary)
Item No.
Location

MITPOP - 200 items
UNID (primary)
Item No.
Alias No.

The MITMAS table holds all the basic data for all the items I have and the Item No. is not duplicatable in the table. The Item No.s are duplicatable in the other 2 tables but not all Item No.s are listed.
The aim is to get a table with all the Item No.s from the MITMAS table and have their matching Alias No.s and Locations. Then, for example, when I allocate a Location to a MITMAS Item No. that didn't previously have one it would create a new part in the MITLOC table and pull the Item No. from the MITMAS table.

So far I have created a relationship (one-to-many) from the MITMAS Item No. to each of the Item No.s in the other two tables with a join type including all entries from MITMAS. I have Queryed the relationship and this gives a table with all the Item No.s from MITMAS and the Alias No.s and Locations where there is a match. I have set the RecordsetType to Dynaset (Inconsistant Updates) to open the fields to editing.

However when I put in a Location into a field that didn't previously have one it comes up in the MITLOC table as a new item but hasn't pulled the Item No. data from MITMAS. How can I create this relationship?

Clear as mud?

Thanks
Hugo

Looking at this and noting that you have no nibbles, I think I must agree, clear as mud.

It is not clear how you are going about the update. In theory, an update should be automatic even for tables linked by a join. Therefore, I have to assume there is some sort of problem holding back the update. If, for example, the record selection would be ambiguous, that would do it. I.e. if Access doesn't know what record it must update, or if there is uncertainty about what is to be create, it will just not do it.

Somehow, even reading your explanation twice, I got lost in it. Consider rephrasing your question.