make query updatable (1 Viewer)

[ClaraBarton]

From everything I've read, if you need calculations, and you create them and then create a left join, the recordset should be updatable. This one isn't. Is there a way I can fix it so it is?
This is the transaction query I'd like to have updateable:

SELECT tblTransactions.fAccountID, tblTransactions.TransactionID, tblTransactions.CkDate, tblTransactions.Num, tblTransactions.Payee, tblTransactions.Cleared, tblTransactions.Debit, tblTransactions.Credit, tblTransactions.QuickenNo, tblTransactions.Modified, qryCatTotals.CatAmount, qryCatTotals.CkAmount, qryCatTotals.Remaining
FROM tblTransactions LEFT JOIN qryCatTotals ON tblTransactions.TransactionID = qryCatTotals.fTransactionID;

This is qryCatTotals:

SELECT tblCheckCat.fTransactionID, Sum(tblCheckCat.Debit) AS CatDebit, Sum(tblCheckCat.Credit) AS CatCredit, [CatCredit]-[Catdebit] AS CatAmount, Sum(tblTransactions.Debit) AS CKDebit, Sum(tblTransactions.Credit) AS CkCredit, [CkCredit]-[CKDebit] AS CkAmount, ([CkAmount]-[CatAmount]) AS Remaining
FROM tblTransactions LEFT JOIN tblCheckCat ON tblTransactions.TransactionID = tblCheckCat.fTransactionID
GROUP BY tblCheckCat.fTransactionID;

 

[arnelgp]

you can try using DLookup()? instead of Joining.

 

[ClaraBarton]

yeah, probably.

 

[The_Doc_Man]

OK, I deconvoluted the query. What we can't see is qryCatTotals. I'm guessing that it is an aggregate query of some kind. From the name, I infer some kind of summation.

SELECT 
    tblTransactions.fAccountID, 
    tblTransactions.TransactionID, 
    tblTransactions.CkDate, 
    tblTransactions.Num, 
    tblTransactions.Payee, 
    tblTransactions.Cleared, 
    tblTransactions.Debit, 
    tblTransactions.Credit, 
    tblTransactions.QuickenNo, 
    tblTransactions.Modified, 
    qryCatTotals.CatAmount, 
    qryCatTotals.CkAmount, 
    qryCatTotals.Remaining
FROM tblTransactions LEFT JOIN qryCatTotals 
    ON tblTransactions.TransactionID = qryCatTotals.fTransactionID;

From your comments, I don't think this JOINed query is your problem. It is the contained query, qryCatTotals, that won't allow the update. If that query is not updateable on its own then it will not be updateable when JOINed with a table. I can see the SELECT working great for you - but to update through this query means you have to be able to update through its components. That is an all-or-nothing situation. And aggregate queries don't really update too well.

As to

Is there a way I can fix it so it is?

The problem is the strategy of trying to display information and allowing it to be updated at the same time. You probably will need separate queries for display and for performing an update. What would you update in qryCatTotals anyway? If qryCatTotals contains data related to tblTransactions, you only want to modify the individual transactions. The totals in the query are technically bound but not uniquely bound to a single record. And that "not uniquely bound" is the problem. If you want to update through a query, Access must point to a single place to update. The presence of aggregates confuses that issue badly.

 

[ClaraBarton]

OK, point taken. Thank you for explaining.

 

[arnelgp]

do you have Index on TransactionID? you should and should be Unique.

 

[ClaraBarton]

I do. I've listened to you all enough...

 

[Pat Hartman]

From everything I've read, if you need calculations, and you create them and then create a left join, the recordset should be updatable.

That's not the way it works. For the query to be updateable, every part of the query must be updateable.

 

[ClaraBarton]

gotcha! thankyou

 

[CJ_London]

It sometimes works to change the query (and form) recordset type from dynaset to dynaset inconsistent updates

 

[ebs17]

This is the transaction query I'd like to have updateable:

What exactly do you want to update?
The calculated values?

Stupid solution: Write the query contents to a new table using a make table query. Then you can change, but you no longer have any reference to the actual data.

If you don't like the calculated values, go back to the base (=> tblCheckCat) and make changes and additions there.

 

[ClaraBarton]

I'm using the transaction query on a continuous form. Fine.
To get some calculations on a detail form I made the qryCatTotals. I want to know what is remaining after the transaction has been separated into categories.
Check Amount less Category Amount = Remaining Amount.
qryCatTotals has every value I need.
So you'd think this would work: =Dlookup("Remaining", "qryCatTotals", "TransactionID =" & Me.fTransactionID)
But it doesn't.
It returns #name.

 

[Gasman]

Only when everything is spelt correctly and the syntax is correct.
Is ftransactionid correct?

 

[ebs17]

I want to know what is remaining after the transaction has been separated into categories.

What does watching calculations have to do with query updateability?
Does the topic title make sense?

 

[Pat Hartman]

Stupid solution: Write the query contents to a new table using a make table query.

Bad practice. If you do that, you'll need to set the compact on close to yes to compensate or the db will bloat eventually until it is unusable.

Rather than joining to a totals query (which is what is making the bound recordSource not updateable), use a subform for the totals queyr or give us more specifics and we'll help with some other option.

 

[arnelgp]

create new Query, and use it as Recordsource of your form:

SELECT tblTransactions.fAccountID,
    tblTransactions.TransactionID,
    tblTransactions.CkDate,
    tblTransactions.Num, tblTransactions.Payee,
    tblTransactions.Cleared,
    tblTransactions.Debit,
    tblTransactions.Credit,
    tblTransactions.QuickenNo,
    tblTransactions.Modified,
    qryCatTotals.CkAmount,
    Dlookup("CatAmount", "qryCatTotals", "fTransactionID = " & Nz([TransactionID],0)) As CatAmt,
    Dlookup("CkAmount", "qryCatTotals", "fTransactionID = " & Nz([TransactionID],0)) As CkAmt,
    Dlookup("Remaining","qryCatTotals", "fTransactionID = " & Nz([TransactionID],0)) As CRemain
FROM tblTransactions;

 

[ClaraBarton]

Ah! finally! put the lookups in the query! I'm on it. Thank you

 

[ClaraBarton]

Worked perfectly, Arnelgp! Thank you so much!