There may be other reasons for your active table, but just from the description here, it would seem a much simpler design to put a field in Clients with a field where you use a value list to designate that client record as "active" or "inactive".
But using your current design, Count ( Active::_pkTrainierID ) , if defined in a calculation field in Trainers would give you the total number of active clients for each trainer.
When I did what you suggested I get a 1 for all trainers. I don't have _pkTrainerID in Active table so used _pkClientStatusID.
What would the calculation field be for your first suggestion with the design being put into Clients table?
So I set it up like you originally suggested and used this calculation field in Trainers table:
Count ( Trainer; Active::Status = "Active" )
Can't get it to work. :(
Hoping for help on this...bumping
I meant count ( Active::_fkTrainerID )
count any field in active that is never empty.
Be sure to define this as a calculation field in trainers. Make sure to select the correct "context" in the drop down of Specify Calculation for this field.
Make sure that this is not an auto-enter calculation either.
I am so sorry but I do not know what I am doing wrong and it is frustrating the crap out of me. I did what you said and create a calculation field in Trainer table, count ( Active::_fkTrainerID ) , and the results comes back as 0
"Make sure to select the correct "context" in the drop down of Specify Calculation for this field.
There are two ways that a field can show a calculated result short of a script setting the field to a calculated result. You can define the field as a field of type calculation and define the calculation. You can also open field options for a number, date, text, etc type field and use the field options tab to specify an auto-enter calculation. Don't do the latter.
What you describe sounds like your relationships are not actually set up as you described in your original post. Can you upload a screen shot of the relevant portion of Manage | Database | Relationships?
Here you go. Thanks
I am also trying another method. In Table Clients I created text field, ClientStatus. Then I create a Value List (Active, Non Active) for field ClientStatus. In Table Trainer I create a calculation field, ActiveClients and use Count ( Clients::ClientStatus ) and it gives me total for Active and Non Active for each trainer. Instead of just total of Active clients for each trainer.
These are not the relationships that you described at the beginning of this post and that's why the suggested solution that I posted is not working.
Count ( client::ClientStatus ) should give you a count of all active and nonactive clients as there is nothing there in place to distinguish between active and non active clients.
Define a summary field in clients. Set it up as the "count of" ClientStatus (any field in client that is never empty will do).
Put a one row portal to clients on your trainers layout. Define a portal filter : Clients::ClientStatus = "Active". Put the summary field in this portal row and it will show the number of active clients for this trainer.
Thank you so much!! That worked! Just have one more quick question. How do I add a total of the Active clients at the bottom?
Thank you for your help and patience!
You can't with the method described as it is a "display only" method. You can't access the total shown in a calculation. you'd need to use a different method for counting your active clients in the first place.
Here's another method:
Define a calculation field in trainers: constActive. Put "Active" as the only expression in the calculation and select "text" as the result type.
Now set up this relationship:
Trainers::__pkTrainerID = Clients|Active::_fkTrainerID AND
Trainers::constActive = Clients|Active::Status
(I didn't flip back to the previous "page" to see your actual field names so substitute your names for mine in the above example. Clients|Active is just a name for a new Tutorial: What are Table Occurrences? of the Clients table.
Thank you! So created it exactly was you described. Not sure what I am suppose to do next. I have the portal with summary_ActiveClients field in there so it shows me the active clients for each trainer. Not sure where I am suppose to put the other field to show the total active clients. Would like that to be underneath the last trainer listed that sums up the total active clients.
Count ( Clients|active::_fkTrainerID )
Defined in Trainers will give you the count.
A summary field that totals this calculation field will give you the grand total.
No portals are needed with this method.