How do I summarize data, like in a report, for use within that same table? Can I use Variables (app wide variables) to do this? It doesn't appear that you can use formulas in Variables. Basically I want to filter data from records in a table, & use the number of records found in a formula within the same table. Is this possible?
I am not sure I understand the specifics of what you are trying to achieve but understand that you need to use a variable in a report filter so will demonstrate below a crude example of doing this, hopefully you can apply this method to your particular circumstances.
You can use app variables as the basis for report filters. An example of this may be a current year set as an app wide variable, assuming you already have a date field you want to use (called [Date] for this example below).
Create an app wide variable called CurrentYear with no spaces and enter 2015 as the variable.
Then create a formula text field called [Current Year] with the formula [CurrentYear] this will now make the variable appear on every record within that table within the field [Current Year].
You then need to do another formula text field called [Record Year] to extract the year from your date field using ToText(Year([Date]))
Then in your report filter set where [Record Year] is equal to the value in the field [Current Year]
This will mean you report will only show records with a date in 2015 if that is what your variable is set to.
Once you have the relationship, at the at the left side of the relationship you will see a button to create a summary field. So you would use that to for example summarize the number of tasks in a Project but that summary count would now be at the project level.
But then, no problem, you would simply do a look up of the total number of Tasks for the Project down to the child table. That would mean that now each individual task of that project is aware of total number of Tasks for the Project and that field can now be used in a formula.
Then make your relationship and use that formula numeric field which you might call link to summaries table as your reference field on the right-hand side of the relationship.
Then if you like you can do as many summary field that you like perhaps summarizing either all of your records or some of your records based on a particular filter. And then once again you just look up those totals down to the detailed records