Using Less Than and Greater Than in Relationship

[crazybake]

Table A:
StartingNumber 
EndingNumber 
Active

Table B:
NumberOne: Calc=1
CalcNumber
Count

I created a relationship TableA::Active to TableB:NumberOne, but I am struggling to finish.
The goal is to get a Count of all Active people matching the CalcNumber - So each record where the CalcNumber >= StartingNumber and CalcNumber <= EndingNumber.  The screenshot show the issue

[rwoods]

Hi there

You are pretty close in what you describe already, you just need to setup the relationship exactly as you describe, then create a calc field in table A to summarise the result.

Since the advent of ExecuteSQL most expert developers would use an SQL statement to get the result you need without using a relationship on the graph. Often we try to reserve relationships on the graph for the data connections and to show portals, rather than to perform summary's in this way, but for smaller and simpler solutions, it is perfectly fine.

I've enclosed an example file if that helps and a screenshot of the results. I just created two FileMaker windows to show both tables side by side.

 

Example.fmp12

[comment]

5 hours ago, crazybake said:

The goal is to get a Count of all Active people matching the CalcNumber

I find this very confusing. Which one of your tables is the table of People? The Active field is in Table A, but the CalcNumber field is in Table B - so what exactly does  "matching the CalcNumber" mean?

[crazybake]

To make the request simple, I did not include all fields.

Table A is my Contacts Table that has all of the personal information, including the Active field, Starting and Ending Numbers.

What I want to do is Count the records where the Active field is checked, but only when CalcNumber >= StartingNumber and CalcNumber <= EndingNumber for each record. The issue in my screen caps above is that for the first record, I want it to stop counting after it reaches the EndingNumber

[comment]

24 minutes ago, crazybake said:

What I want to do is Count the records where the Active field is checked, but only when CalcNumber >= StartingNumber and CalcNumber <= EndingNumber for each record.

Does the attached work for you?

Example2.fmp12

[crazybake]

That is it... and I had thought that was the first relationship I created.

Thanks for the simple solution.