Solved

Access 2007 Query - Operation must use updateable query

Posted on 2010-09-19
11
1,144 Views
Last Modified: 2012-05-10
Hi Experts,

Can you please help me with the following query. Not sure why it isn't working.

UPDATE tblfinancialtransactions
SET tblfinancialtransactions.TransactionAmount =
(
SELECT Sum(tblFeeSchedule.Fees) AS SumOfFees
FROM (tblClassProducts
   INNER JOIN (tblClassMeeting
   INNER JOIN (tblAttendance
   INNER JOIN (tblFinancialTransactions
   INNER JOIN tblInvoiceLineItems
     ON tblFinancialTransactions.TransactionID = tblInvoiceLineItems.TransactionID)
     ON tblAttendance.AttendanceID = tblInvoiceLineItems.AttendanceID)
     ON tblClassMeeting.ClassMeetingID = tblAttendance.ClassMeetingID)
     ON tblClassProducts.ClassProductID = tblClassMeeting.ClassProductID)
   INNER JOIN tblFeeSchedule
     ON tblClassProducts.ClassFeeID = tblFeeSchedule.ClassFeeID
HAVING (((tblInvoiceLineItems.TransactionID)=42))
)
WHERE (((tblfinancialtransactions.transactionID)=42));

Thanks
RiC
0
Comment
Question by:RiCzN
  • 5
  • 5
11 Comments
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 33714662
Ric,

It would be good if you can post a sample db having the tables included in the SQL query you posted. Include as well the Update query that you design for this.
Other objects like forms, reports, etc. may not be included.

Sincerely,

Ed
0
 
LVL 4

Expert Comment

by:tabish
ID: 33715125
How you are running this query? is it being run from a front end like a webpage or any other app or you are running it within Access?

If a front end is being used then you should check the permissions to the db.

If the database is on a network drive where multiple users can access it then  locking machanisin can be a problem too.

If none of the above then you might need to look at the joining tbles in sub query if all of them have the unique index creted. if even one table is without a unique index then it might be a problem.

My experience is not very good with Access when it comes to writing a complex update query.

If you like, you may want to achieve it in 2 steps.

1 - First Create a table (Make Table query) from the sub query you are using. - Complex logic will be turned into a plane table.
2 - Use that created table to update the actual table.
0
 

Author Comment

by:RiCzN
ID: 33717154
Hi Ed and Tabish,

I'm trying to create an update query to:
1. input into the financial transaction table the TotalAmount; by
2. summing up the line items in the tblInvoiceLineItems; and that
3. Each line items correspond to the transactionid on the form.

If you take a look at the frmInvoice and InvoiceLineItems table for example, what you'll see is:
1. TransactionID = 42
2. In InvoiceLineItem there are 10 lines with different AttendanceID.
3. AttendanceID indicates the attendance a student have taken in a particular class.
4. The classes student attends may vary in fees so query needs to check against AttendanceID for fees to be incorporated into the total amount.

Intended structure of the query.
1. Query to note TransactionID then
2. Go to tblInvoiceLineItems and grab all AttendanceID belonging to the TransactionID
3. Lookup the  fees from tblFeeSchedule that correspond to the AttendanceID
4. Add to find total amount.
5. Input this total amount into tblFinancialTransaction in the field TransactionAmount where TransactionID matches  that in step1.

Hope that clarifies what i'm trying to do.
Thanks
Ric
related-tables.jpg
frmInvoice.jpg
Invoice-line-items.jpg
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 33718175
Ric,

To test this code, create a button in your frmInvoice. In the click event of the button, copy and paste the code below:

DoCmd.RunSQL "UPDATE tblFinancialTransaction SET tblFinancialTransaction.TransactionAmount= [Forms]![frmInvoice]![txtTotal]
WHERE (((tblFinancialTransaction.ID)=[Forms]![frmInvoice]![TransactionID]));"

The said code will put the Total amount into TransactionAmount of tblFinancialTransaction where the TransactionID is equal to TransactionID in frmInvoice.

Let me know whatever error that comes if ever there is.

Sincerely,

Ed
0
 
LVL 19

Accepted Solution

by:
MINDSUPERB earned 500 total points
ID: 33718214
I forgot to mention that I just assumed txtTotal as the field name of the text box that display the Total in frmInvoice.

Theres also another way to do it:
1. Create a new query and display it in SQL View.
2. Copy and paste the SQL statement below:
UPDATE tblFinancialTransaction SET tblFinancialTransaction.TransactionAmount= [Forms]![frmInvoice]![txtTotal]
WHERE (((tblFinancialTransaction.ID)=[Forms]![frmInvoice]![TransactionID]));
3. Name the query as qryUpdateTransactionAmount
4. On the click event of the button in frmInvoice, write this code:
DoCmd.OpenQuery "qryUpdateTransactionAmount"

Sincerely,

Ed
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:RiCzN
ID: 33718350
is the footer of the subform with sum on all fees
0
 

Author Comment

by:RiCzN
ID: 33718398
Please see attached form design
from-design.jpg
0
 

Author Comment

by:RiCzN
ID: 33718480
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 33718507
Yeap. So if it is in the subform, change this part of the code
[Forms]![frmInvoice]![txtTotal]  into

[Forms]![frmInvoice]![subInvoiceLineItems].[Form]![txtTotal]

For the txtTotal, see what is the name of that textbox in the property window.

Ed



Ed
0
 

Author Closing Comment

by:RiCzN
ID: 33718615
Thanks. Pointed me in the right direction.
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 33718674
Ric,

Glad that you followed it well. Thanks as well for the grade.

Ed
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now