I have an application that accepts nominations for an award. I want to be able to count the number of nominations submitted and when the number gets to ten I want to display a message that says something like, 'nominations for this category are closed'. There are six different award categories, each on its own table. How would I do that?
Make a single new table called Submission totals.
Add one record to it, even though it has no fields yet.
It will be [Record ID#] =1 as it's the first record.
Make a field on Submissions Category 1 called [Link to submission Totals] and make it a formula numeric field with a formula of just
1 (i.e. The same as the Record ID #1)
Now make a relationship back to the Submissions totals table based in this field being the reference field on the right side of the relationship.. One Submissions Total has many Submissions.
Finally, on the left side of that new relationship, make a summary field to count the goal submissions. Then look that up down to the to the Sbmissions table 1.
That's it. The Submissions table 1 now knows the count of the total submissions so you can have form rules or a formula text field to control what the users see.
Unfortunately, given your decision to have 6 tables for 6 awards instead of a better design of 1 table and the user choosing an award category at the top of the form, you will need to repeat this process for each award submissions process, although you still only need to have that single table to hold the totals.
It would be a better design to have a table of Award Categories, having many award submissions and then set the limit up on the Award Categories table and thus only have two tables.