I have created a query to pull information out of various tables to eventually use in a form. I can edit the information inside that specific table but not in the query itself.
Can anyone see why this is happening?
I have created a query to pull information out of various tables to eventually use in a form. I can edit the information inside that specific table but not in the query itself.
Can anyone see why this is happening?
Does your query use a JOIN? When queries retrieve data from more than one table, they are generally not updatable. In order to update a query with a JOIN, you need to start by making sure the JOIN represents the proper relationship and the JOINED columns are Indexed.
I have to disagree with that statement. I use queries that join multiple tables that are updateable all the time. It all depends on the type of relationship that exists between the two queries.Does your query use a JOIN? When queries retrieve data from more than one table, they are generally not updatable.
See the following for reasons why certain ones are not updateable:
http://rogersaccessblog.blogspot.com...eable-why.html
http://www.techrepublic.com/blog/mic...s-wont-update/
Disagreeing is certainly your prerogative. However, I am not sure why you quoted me out of context.I have to disagree with that statement. I use queries that join multiple tables that are updateable all the time. It all depends on the type of relationship that exists between the two queries.
See the following for reasons why certain ones are not updateable:
http://rogersaccessblog.blogspot.com...eable-why.html
http://www.techrepublic.com/blog/mic...s-wont-update/
So how do I change mine so the relationship allows updating?
I was simply specifying that part of the statement I disagree with. I think that part of your statement is just too broad/general a statement, and is not necessarily true. It is really dependent on the underlying factors.Disagreeing is certainly your prerogative. However, I am not sure why you quoted me out of context.
From my sixteen years of personal experience, most of the queries are I write are updateable (making sure that your database tables are normalized and the queries are constructed properly helps).
I cannot download your database to take a look at it from my current location.So how do I change mine so the relationship allows updating?
I would recommend going through the links I provided, and see if you can determine what situation your query falls into that is preventing it from being updateable.
The first thing to check is the nature of the relationship. Is it many-to-many?
sometimes you can do it by changing the recordset type to dynaset - inconsistent updates, but not in this case. The reason why is because of the multiple link between factories and engineers. Remove the Site link in the query and the query will become updateable.
You also need to be clear about 'updateable' - it is one thing to change a value in an existing record, quite another to add a new record - in your query you are showing SiteID and FactoryID only once (for the Sites and Factories tables respectively), if you added a new record based on this then both new Factory and Engineers records would not have their family keys populated and become orphans.
If the factory/engineer relationship is valid then you will need to handle it in forms - the engineers would be in a subform and you would set the linkchild and linkmaster properties of the subform to
FactoryID;SiteID
then remove engineers from your query