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!
DCount
I can say, that it works and the performance is good enough. If things get bigger (and therefore slower), I'll come back to your suggestion.