You would need to start with your data model: What tables and relationships form the "foundation" of your solution:
Clients----<Shows-----<Jobs (---< means "one to many")
Clients::__pkClientID = Shows::_fkClientID
Shows::__pkShowsID = _fkClientID
Fields that start with __pk are "primary keys" that auto-enter a serial number or UUID. Fields that start with _fk are "foreign keys" that store a value that matches a primary key in a related table.
With the above data model, you can set up a layout based on Shows to list all shows and include fields from Clients as needed to identify each client. A calculation field in Shows can be defined as:
Count ( Jobs::_fkShowID ) to show the number of jobs for a given show.
So, do I have to create two new tables?
Or you could use a sub summary report with two parts, the first on client name, the second on show name. With in the subsummary parts use record count to get you the number of jobs. Leave the body element in the report while developing to test and check but you can remove this from the finished report.
Yes, that option also works and may be what you need, but there is one key difference:
With the first approach you can have an entry where there is a client with a show and 0 jobs. With the second approach, such an entry will not appear in the report. Whether or not that is possible or significant is a call that is up to you.
While you should certainly pay attention to Phil's advice about structure, here is another useful technique for setting up a record counter.
Within any table create a Summary field set to Count any field that is certain ti contain a value—most likely the primary key:
This single field can be used in a number of ways.
1. Within the table itself it will always maintain a count of the found set:
2. Drawn from a related table (i.e. the table inside a portal, say) it will maintain a count of related records:
That is just two examples. You could, of course, create counter fields in other ways, but this is a method which enables you to use the same field for many different types of count, thus minimising the number of fields you require.
What is more, this is a tip I picked up on this very forum!
Thanks! All of these are so helpful.
Here's a better description of my need.
I have a table called " clients" that has these fields, among others
How can I create a portal (to show on my master layout, showing the table JobLog) that just shows each unique "Show" with a total number of the jobs (Job Name) it has So it would look like this:
Show 1 6
Show 2 8
Show 3 10
No need to show name of job.
You wouldn't use fields in clients called "show" or "job name" as this data to store in multiple related records.
A list view layout based on jobs can reproduce that layout without having to duplicate client and show data even though such a layout will still list the needed show and client data in every row.