Why is my query result not updateable?

[skiller]

Hi all,

I have a simple query involving 2 tables with an inner join and the resulting recordset is updateable.

But when I add another table to the query which is inner joined to both of the other 2 tables then the resulting recordset is now not updateable.

Can anyone explain why?

My query which is updateable is as follows:

SELECT
  dn.OrderNo,
  di.DelNoteNo,
  di.ItemNo,
  di.ProdCode,
  di.ValueToEdit

FROM
  tblDeliveryItem di
  INNER JOIN
  tblDeliveryNote dn
  ON di.DelNoteNo = dn.DelNoteNo;

My query which is NOT updateable is as follows:

SELECT
  dn.OrderNo,
  di.DelNoteNo,
  di.ItemNo,
  di.ProdCode,
  di.ValueToEdit,
  oi.Roy

FROM
  tblOrderItem oi
  INNER JOIN
  (tblDeliveryItem di
   INNER JOIN
   tblDeliveryNote dn
   ON di.DelNoteNo = dn.DelNoteNo
  )
  ON oi.ProdCode = di.ProdCode AND oi.OrderNo = dn.OrderNo;

I have attached the tiny example DB for anyone who would be kind enough to offer an explanation.

Thanks!

 

[The_Doc_Man]

Look at this article.

Operation must use an updateable query, This Recordset is not updateable: Dealing with non-updateable queries in Microsoft Access

Operation must use an updateable query: Using temporary tables to bypass the limitations of recordsets which are not updateable

www.fmsinc.com

I rarely open someone else's database so I will guess that somewhere in the mix, you have something that causes an ambiguity in writing - in that for a given set of keys, more than one record might be selected. Updates require unique targeting and thus if your write-targets are not unique, Access will not know to which record it should write the results.

 

[skiller]

Hi DocMan, thanks for your reply.

The link you posted to fmsinc is one of the first things I read when I Googled the issue so I've already read that, but thanks all the same.

Your last paragraph includes some good tips, but I can't see how any of those apply to the issue I have unfortunately.

After some significant Googling, it seems Access just sometimes decides to make a recordset non-updateable even when the records are obviously unique.

 

[arnelgp]

you should concentrate first on your table structure.
start with order table. it should have autonumber (unique identifier),
for each record.
then go to delivery table, again add a FK to connect to order table.

delivery note is not connected to both of the table.
although the info here is inserted to delivery table.

 

[Pat Hartman]

You have two joins to the same table creating a circle. One of the joins is data field to data field and will therefore result in a Cartesian Product which will not be updateable. It also could create a conflict since the same record from one join may not be able to satisfy both joins.