I have a query that is used to combine data from several tables. While the query is open, if the data is changed, I would like the query to display the updated data. Most of my queries do this, but I'm having problems with one specific query.
I believe I have narrowed down the cause, and I'm looking for somebody to either confirm that what I believe I've found, or to point me in a new direction.
In my database, I have a main table (tblItems). This table is linked to another table (tblLocations) using a many-to-many relationship (via tblLocationItems). The main table also links to another (tblItemTypes) via a one-to-many relationship.
I created a select query with tblItems and tblItemTypes, on tblItems.TypeID = tblItemTypes.TypeID (foreign key to primary key). This query displays tblItems.ItemID (primary key), tblItems.TypeID (foreign key), and tblItemTypes.TypeName (related field). If I open this query and then update tblItems.TypeID directly from the table (or via code), the TypeName field automatically updates as well. This is how I want it to work.
I then linked in tblLocationItems to the above query on tblItems.ItemID = tblItemLocations.ItemID (primary key to part of a multi-field primary key). At this point, if I update tblItems.TypeID, the TypeName field no longer updates (however, the TypeID field does successfully update).
Based on my experiments, I have come to believe that when you include a table with a multi-field primary key (such as in a many-to-many relationship), your query will no longer automatically update fields pulled from related tables. However, I have been unable to locate any documentation to indicate that I am either correct or incorrect.
Can anyone either confirm or disprove my theory? I want to get this to work, but I don't want to waste time if it can't be done.
I believe I have narrowed down the cause, and I'm looking for somebody to either confirm that what I believe I've found, or to point me in a new direction.
In my database, I have a main table (tblItems). This table is linked to another table (tblLocations) using a many-to-many relationship (via tblLocationItems). The main table also links to another (tblItemTypes) via a one-to-many relationship.
I created a select query with tblItems and tblItemTypes, on tblItems.TypeID = tblItemTypes.TypeID (foreign key to primary key). This query displays tblItems.ItemID (primary key), tblItems.TypeID (foreign key), and tblItemTypes.TypeName (related field). If I open this query and then update tblItems.TypeID directly from the table (or via code), the TypeName field automatically updates as well. This is how I want it to work.
I then linked in tblLocationItems to the above query on tblItems.ItemID = tblItemLocations.ItemID (primary key to part of a multi-field primary key). At this point, if I update tblItems.TypeID, the TypeName field no longer updates (however, the TypeID field does successfully update).
Based on my experiments, I have come to believe that when you include a table with a multi-field primary key (such as in a many-to-many relationship), your query will no longer automatically update fields pulled from related tables. However, I have been unable to locate any documentation to indicate that I am either correct or incorrect.
Can anyone either confirm or disprove my theory? I want to get this to work, but I don't want to waste time if it can't be done.
