Locked checkbox with group by query

[russiver]

I have an accounts database that stores bank transactions in one table with a 'one to many' (transID to FKtransID) relationship to a second items table that
stores the items of that transaction.

The transactions table includes a yes/no field for checking off when a bank statement is received.

Ideally, I would like to create a query or a form based on a query that lists all transactions with a sum of the cash amount for all the items within a transaction - grouped by FktransID.

However, the group by statement in the query locks the statement checkbox so transactions cannot be ticked off.

Can anyone suggest a workaround?

 

[pbaldy]

A DSum() for the sum of items is an option.

Microsoft Access tips: Why is my query read-only?

List of reasons why a Microsoft Access query will yield non-editable results.

allenbrowne.com

 

[russiver]

Thanks. Will give that a try.

 

[June7]

Should interact with form, not table or query. Use form/subform arrangement. Use Sum function to show sum of items in subform. If subform is in DataSheet view, use Totals button on ribbon to show a Totals row.

Domain Aggregate functions can cause slow performance in queries and even in textbox calculations.

 

[russiver]

Thanks for all the replies. Although a little slow, using DSum() for the sum gives me the check list I was looking for.

 

[June7]

As noted in post 4.