Query is read-only probably due to an aggregated value

Question

There is an existing MS-Access project (not done by me), where people want to get an additional column into a data sheet for advanced filters.

Just imagine a simple straight-forward query filled to a form in data sheet view. People can use built-in filters and sort options.

For the underlying table (let's call it MainTable) there is another related table with audit data (call it AuditTable). Any change in MainTable is written to this AuditTable and must be accepted by a super-user in another process.

Now I want to add the count of audit lines which are not accepted yet to the visible data sheet. Something like

SELECT Count(*) AS OpenAudit 
FROM AuditTable 
WHERE MainTableID=MainTable.ID --<-- MainTableID is the FK in AuditTable onto the ID of MainTable
  AND Accepted=0
GROUP BY MainTableID

It works great to add this to the underlying query and display the value in a new column. Filtering, sorting, all works well. I tried it as well as a sub-selected column and as a side query joined in the from clause.

But now there is this problem:

The data sheet is read-only suddenly. Users cannot change any data there anymore.

I found, that a query containing aggregated data and/or sub-selects or queries constisting of stacked queries will lead to read-only recordsets (list by Allen Brown).

And here's the question:

Is there any approach to include such data in a recordset without changing the RecordSource to a read-only set?

Some simple sample data

MainTable
ID    SomeValue   OneMore
 1       val 1      more 1
 2       val 2      more 2
 3       val 3      more 3

AuditTable
ID    MainTableID   Accepted  --(+ more columns with fieldname, valueBefore and valueAfter etc)
 1         1            1
 2         1            0
 3         2            1
 4         3            0
 5         3            0

 The expected Result
 ID    SomeValue   OneMore    CountOfOpenAudits
 1       val 1      more 1            1
 2       val 2      more 2            0
 3       val 3      more 3            2

This additional column should be somehow visible in the user's GUI without changing the recordset to read-only.

Hope this is clear, TIA!

Answer 1

You could use a domain aggregate function such as DCount, though, it will be slower.

For example:

select t.*, dcount("*","AuditTable","MainTableID=" & t.ID & " and Accepted=0") as OpenAudits
from MainTable t