Search code examples
ms-accessms-access-2016

How can I add a "Calculated" field in a table that outputs the count of records in the subdatasheet for that record?

For example,

enter image description here

enter image description here

The number of instances field shows "1" because there is one record in the subdatasheet associated with the main record. (I currently have the calculated field set to "1" for all records as a placeholder)

Is there a way to have this calculated for every main record automatically?

Solution

  • Access doesn't have calculated columns that update automatically. You can have a calculated column in a query by defining a column based on a DCount() function.

    Another way to do it would be to create a master/detail form pair with both forms set to display in datasheet view. This will present the same way as the raw table datasheet with expanding subrows. Add a text box to the parent form with a function in the ControlSource to determine the count of rows in the detail dataset. A DCount function would work there too.

    =DCOUNT("*", "DetailTableName", "ForeignKeyColumnInDetailTable = '" & [KeyColumnControlNameOnMasterForm] & "'")

    If the data type of the key is a string value, you'll need the single quotes in the example. If the data type is numeric, the single quotes aren't needed, so the final concatenation at the end would go away completely. If you happen to be referencing a date, replace the single quotes with hash tags.