Locked checkbox with group by query (1 Viewer)

[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.

 

[Pat Hartman]

I'm confused about what you are trying to do. If you are summing the details, you can't check individual items.

Use a main form for the parent table and a subform for the details table. That lets you show the sum in the footer of the detail form as well as allowing you to check the received flag on the parent table.

The two things you are trying to do are incompatible in one query.

 

[russiver]

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

 

[Pat Hartman]

You don't need to use DSum() if you are using a form.

 

[June7]

As noted in post 4.