Many to one to many query results in message "This Recordset is not updateable"

[NotAProgrammer]

I have been getting the status bar on my query reading "This Recordset is not updateable."

I have a table, [tblFixturesAndTools] that holds all of the information on a tool, including things like Tool Number, description, machine, picture, etc... I have a second table, [tbrToolUsageByProduct] that only has 3 fields: ID, ToolNumber, ProductNumber. This table is used to relate a given product to the tool that is needed, where the joining field is ToolNumber. I have a third table, [tblPlugAndFittingsProcesses] that also has just 3 fields: ID, ToolNumber, Process. This table is also joined to the main table, [tblFixturesAndTools] by the field ToolNumber. All three tables are joined through the same field on each table, "ToolNumber".

After many search's and other forums I finally found someone mention my specific problem, but no solution was offered. Here is the excerpt I found from another site, "When Recordsets Are Never Updateable: 9) Query based on three or more tables in which there is a many-to-one-to-many relationship" -Rogers Access Blog. According to Rogers blog, the recordset can never be updateable as a many to one to many. I don't know how to set up a new relation to fix this issue.

In all of the examples of many to many relationships, the joining table did not share the same field for both of the many's, so i think this is an odd case of a many to many relationship.



I have included a screenshot of the design view of my query.

I am not sure how to resolve this issue but it is necessary that this query is updateable so that the form it drives can be edited. Any Suggestions on restructuring this query to get the same effect but allow it to be updateable?

[June7]

General rule is a form should edit one table.

Use form/subform arrangement for data entry/edit.

[orange]

You have described the tables you have created. Can you step back and tell us in simple English -no jargon- about the Products and Processes that use "Tools"? Perhaps you could give a few examples so readers can understand your set up as you undertstand it. We only know what you tell us, so details could be important.
Good luck with your project.

[isladogs]

I'm not surprised that this failed as I've had similar experiences myself.
However, I'm going to disagree and say that it can work in some cases.

The OP didn't mention doing this from a form so I don't think those points are relevant here (and I often successfully break those guidelines anyway)

As a test I tried this query on a many to one to many query with 2 outer joins (same as yours)



As you can see I included a field from each table to update and it still worked perfectly
In this case, I was using PK fields in each table

So as a test I removed the PK property from those fields in 1 then 2 and finally all 3 tables.
The update query still worked (to my surprise!)

It would help if you showed us your query design so we can suggest ways you may be able to proceed

BTW: Do you have a link to that webpage so I can read it and possibly respond?

[NotAProgrammer]

Part of our factory is a machine shop. Many of our products require custom tooling and fixtures for those jobs. We have an old database that stores some of that information but it is lacking in a lot of ways, so we are making a new database that can handle our new requirements. We store specific information on the tooling/fixture that we use, indexed by a unique tool number. We then relate that tool number to a product, so an engineer or machinist can search the database by the product and find all the tools they need for the job or look up information on a specific tool/fixture. The database handles a one to many relationship to do simple queries like i just mentioned just fine.

Where the old database falls short and where I got into this problem is some new processes we started doing. We began using plugs and fittings in some of our products for outside vendor services like specialized painting and coatings. Each of the processes we needs has to have specific fixtures and plugs to withstand the processes. For example, we have special plugs just for anodize coating, and then other plugs that we use for paint and gold coat. According to some database tips I read, you should try and minimize repeating information where possible by creating relation tables. So I made a relational table that relates a tool with a given process it is capable of. A plug can have anywhere from one to 5 processes that it is capable of going through and i didn't want to create 5 fields named Process1, Process2, Process3, etc... An engineer needs to be able to add new plugs as we create new products and specify which processes those plugs and fittings are capable of. Then it needs to be able to search by those processes or tool numbers to find that information to make the job.

I hope this adds some clarity to my problem. I am still relatively new to access and using internet forums.

[NotAProgrammer]

http://rogersaccessblog.blogspot.com...eable-why.html

Here is the web page I found this on.

I have included an image of my query.

I also tried the same exact query shown but with the exception of changing the PK to look like yours. A shared primary key of ToolNumber & ProductNumber for one table and then ToolNumber & Process for the other. This did not work for me so I changed the PK back to an autonumber ID.

[isladogs]

That's a SELECT query.
Can you show us the UPDATE query that failed or did I misunderstand what you wanted?

If you want an EDITABLE SELECT query, you need to use 2 tables at a time. In other words do it in 2 steps.

OR create a MAKE TABLE query to make a TEMP query based on your 3 tables.
Then edit the TEMP table
Finally update the original tables from the TEMP table using one or more UPDATE queries.
The TEMP table can now be deleted if its a one off operation or emptied if this is something you will need to repeat

EDIT:
I've now checked that site. Its a very good summary of why queries may be READ ONLY.
I agree with the author that many to one to many SELECT queries are READ ONLY
However as I showed earlier, UPDATE queries based on the same structure may still work

For info, there's another good article here: http://allenbrowne.com/ser-61.html

[NotAProgrammer]

Thanks for the quick replies. I think I am going to take isladogs advice and just try and find a way to do it in 2 steps with 2 tables at a time.

[orange]

NotAP,

Just to clarify your set up, I have listed some of the points from your posts and put a draft model together. The purpose is just o ensure the proposed database is addressing your "big picture" and we aren't focused on some aspect of that big picture.

This material is for consideration, discussion and clarification only. I'm not sure how a plug differs from a Product. Nor if you need to identify Process(es) or Process steps in the "building" of a Product.

Summary of your info:

-In our factory machine shop we undertake jobs to make products -some of which require custom tooling
 and fixtures. 
-We uniquely store specific information on the tooling/fixtures that we use. 
-We relate that/those tool number(s) to a product
-an engineer or machinist can search the database by the product and find all the tools needed for the job 
-an engineer or machinist can look up information on a specific tool/fixture. 

-We have new processes that use plugs and fittings in some products for outside vendor services (eg specialized
 painting and coatings)
-Each of these processes requires specific fixtures and plugs  (eg special plugs  for anodize coating,  other plugs 
for paint and gold coat)
-Engineers add new plugs
-we create new products and specify the processes and their required plugs and fittings
-we need the ability to search  processes or tool numbers to find that information to make the job.


Related info to be considered:
 According to some database tips I read, you should try and minimize repeating information where possible by 
creating relation tables. 
 So I made a relational table that relates a tool with a given process it is capable of. 
 A plug can have anywhere from one to 5 processes that it is capable of going through and i didn't want to create 5 fields named Process1, Process2, Process3, etc...

Rough draft/strawman:

[NotAProgrammer]

I have everything working now except that one query/form. I have found an acceptable way of getting around my problem with the advice from isladogs. It is a more simple and clean way to get the same result but it just adds an extra step to the process, which I can live with.

I would try and explain the whole structure but i feel it is too complicated over a forum for the scope of the entire database. I have attached my relationship tables as an example. The database controls a lot more than just a plug or a fixture but the rest is working at the moment.

I think I have it solved now although with the advice from everyone I realize how little I know about queries besides SELECT queries, so i think I am going to spend some time reading up on the alternatives and other functionalities these tools offer.

Thank you for all the suggestions, I have been stuck on this for days!

[orange]

Glad you have it under control and do have the "big picture"/scope in mind.
Just curious, what is the meaning of your tbr prefix to some tables?

You may get some refinement/adjustment/vetting ideas from this post.

Good luck with your project.

[isladogs]

There are issues with some of your relationships. Confusingly you have tables with identical names but prefixed with 'tbr' and 'tbl'.
The tbr versions of FixturesAndTools and CuttingTools are at the arrow end of joins on both sides.
I believe this really will cause ambiguous joins and I am surprised Access let you set up referential integrity on those joins

Suggest changing all joins to INNER and reviewing your table structure as well.

[NotAProgrammer]

tbr is table relational. It lets me know which tables are dependent on information from other tables, where as tbl is an independent table that doesn't have any dependent information from another table. I know tbr is not a standard practice but it helps me distinguish the tables use from other tables easier.