2

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!

Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • If DCount() destroys performance, about the only other option is to insert the result of your above query into a temp table, and join that to the main table for the record source. But you will need ways to keep the data current when needed. – Andre Dec 16 '19 at 18:17
  • Or, if the underlying data comes e.g. from SQL Server, create and link a view, and base the form on that. – Andre Dec 16 '19 at 18:18
  • 1
    If it's a form, then don't include the aggregate in the record source. Instead, add a textbox and set that textbox equal to the domain aggregate. This allows lazy loading and will improve performance. – Erik A Dec 16 '19 at 18:52
  • @ErikA, thank you for your input. After testing around with `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. – Shnugo Dec 17 '19 at 12:56
  • @Andre, As written below the answer by Lee Mac DCount worked it out pretty well. Thank you once again! – Shnugo Dec 17 '19 at 12:57

1 Answers1

2

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
Lee Mac
  • 15,615
  • 6
  • 32
  • 80
  • 1
    I did a lot with MS-Access some years ago, but I had forgotten the D-functions. Thx, DCount did the trick and the performance is sufficient in this rather tiny case... – Shnugo Dec 17 '19 at 12:55