Update Query

[Diksha1310]

I'm computerizing a bookshop. For my purchase form, i need the following tables: PurchaseID, DatePurchased, QuantityInStock, QuantityPurchased, TotalCost and InvoiceNum.
But,...since they are not found on the same table, i did a query,..but the query does not accept any additions,deletions,etc..its read only.. ! So its difficult to make a form based on it as data cannot be entered or even modified!
Can someone help...! I'm stuck

 

[vbaInet]

Here are reasons why your query is read-only:

http://allenbrowne.com/ser-61.html

 

[Diksha1310]

Can it be because it is one to many relationship!! The other reasons don't fit,..i did another query previously for my book form,..it was NOT read only though! i can;t understand why only this particular one.

 

[vbaInet]

Let's see the SQL statement of the query.

 

[Diksha1310]

SELECT tblPurchase.PurchaseID, tblPurchase.DatePurchased, tblStock.QuantityInStock, tblPurchase.QuantityPurchased, tblPurchase.[TotalCost($)], tblPurchase.InvoiceNumber
FROM (tblSupplier INNER JOIN ((tblBook INNER JOIN tblStock ON tblBook.[BookID] = tblStock.[BookID]) INNER JOIN [tblSupplier-Book] ON tblBook.[BookID] = [tblSupplier-Book].[BookID]) ON tblSupplier.[SupplierID] = [tblSupplier-Book].[SupplierID]) INNER JOIN tblPurchase ON tblSupplier.[SupplierID] = tblPurchase.[SupplierID];

 

[vbaInet]

I believe it's your JOINS.... like you guessed in your other post.

 

[MSAccessRookie]

Can it be because it is one to many relationship!! The other reasons don't fit,..i did another query previously for my book form,..it was NOT read only though! i can;t understand why only this particular one.

You may have overlooked one of the reasons from the list. Based on your SQL Code, the way that your Tables are JOINed could be contributing to the problem. I would look into the folowing as an possible potential issue:

• The fields in a JOIN are not indexed correctly: there is no primary key or unique index on the JOINed fields.

UPDATE: Once again I get interrupted at work during an attempt to reply and wind up posting nearly the same thing as someone else. At least I am in good company.

RESULT? Looking at the JOIN Statements (Order and Index) seems to be the right place to start.
​

 

[Diksha1310]

Yeah, but all my tables have a unique primary key..
is there a soln?

 

[Diksha1310]

Ok!thnx i'll see..

 

[vbaInet]

You may have indexes and PKs but the JOINS would not permit an update. You need to break it down into two parts.