Calculate field based on found set of other table fields

[charlesdab]

I tried to search around for the answer, but I was not successful.

 

I have a table of records which each have a category index.  So, records are assigned a category of "001," "002," "003" etcetera.

 

I also have a category table with the category index and category label.  I am adding a field that is "categoryCount."  I would like to know the number of records in the main table assigned to each category.

 

The end goal is to have a layout that looks like this:

 

001 - "Category A" - 19 Records

002 - "Category B" - 2 Records

003 - "Category C" - 11 Records

 

I had trouble setting up the calculated value to actually count the discrete record sets.

 

Does anyone have any material to point me in the right direction?  Thanks!

[comment]

I would like to know the number of records in the main table assigned to each category.

 

Ordinarily I would say define a calculation field in the Categories table =

 

Count ( YourMainTable::CategoryID )

 

but your title speaks of the found set, while the above will count all the records in your "main" table, regardless of any found set. To summarize the found set, you will need a summary field (defined in the same table), and a layout with a sub-summary by Category part.

[charlesdab]

Ordinarily I would say define a calculation field in the Categories table =

 

Count ( YourMainTable::CategoryID )

 

but your title speaks of the found set, while the above will count all the records in your "main" table, regardless of any found set. To summarize the found set, you will need a summary field (defined in the same table), and a layout with a sub-summary by Category part.

 

I think actually that what you first said would be fine.

 

What would the calculation syntax look like?

 

Thanks!

[Rick Whitelaw]

Exactly as Comment wrote it.