Access 2007 Query - Operation must use updateable query
Hi Experts,
Can you please help me with the following query. Not sure why it isn't working.
UPDATE tblfinancialtransactions
SET tblfinancialtransactions.T
(
SELECT Sum(tblFeeSchedule.Fees) AS SumOfFees
FROM (tblClassProducts
INNER JOIN (tblClassMeeting
INNER JOIN (tblAttendance
INNER JOIN (tblFinancialTransactions
INNER JOIN tblInvoiceLineItems
ON tblFinancialTransactions.T
ON tblAttendance.AttendanceID
ON tblClassMeeting.ClassMeeti
ON tblClassProducts.ClassProd
INNER JOIN tblFeeSchedule
ON tblClassProducts.ClassFeeI
HAVING (((tblInvoiceLineItems.Tra
)
WHERE (((tblfinancialtransaction
Thanks
RiC
Can you please help me with the following query. Not sure why it isn't working.
UPDATE tblfinancialtransactions
SET tblfinancialtransactions.T
(
SELECT Sum(tblFeeSchedule.Fees) AS SumOfFees
FROM (tblClassProducts
INNER JOIN (tblClassMeeting
INNER JOIN (tblAttendance
INNER JOIN (tblFinancialTransactions
INNER JOIN tblInvoiceLineItems
ON tblFinancialTransactions.T
ON tblAttendance.AttendanceID
ON tblClassMeeting.ClassMeeti
ON tblClassProducts.ClassProd
INNER JOIN tblFeeSchedule
ON tblClassProducts.ClassFeeI
HAVING (((tblInvoiceLineItems.Tra
)
WHERE (((tblfinancialtransaction
Thanks
RiC
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.
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.
ASKER
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
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
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.Tr
WHERE (((tblFinancialTransaction
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
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.Tr
WHERE (((tblFinancialTransaction
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
is the footer of the subform with sum on all fees
ASKER
Please see attached form design
from-design.jpg
from-design.jpg
ASKER
Yeap. So if it is in the subform, change this part of the code
[Forms]![frmInvoice]![txtT
[Forms]![frmInvoice]![subI
For the txtTotal, see what is the name of that textbox in the property window.
Ed
Ed
[Forms]![frmInvoice]![txtT
[Forms]![frmInvoice]![subI
For the txtTotal, see what is the name of that textbox in the property window.
Ed
Ed
ASKER
Thanks. Pointed me in the right direction.
Ric,
Glad that you followed it well. Thanks as well for the grade.
Ed
Glad that you followed it well. Thanks as well for the grade.
Ed
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