Using Portals to calc Current Status and Change of Prior Numbers
Thanks to PhilModJunk I think I have a better idea of what I need, but I'm still trying to work out the kinks on this.
I have a weekly report that I want to produce. It currently calculates the Up-To-Date status of Costs and Counts of various Types (Type A-Type F... see attached).
I'd like to add two fields that calculate the Change Over Prior Costs and Counts (calc all Costs and Counts that have changed since the last weekly report).
I have a Main Table that generates a new ID# for each new record. I've created a 2nd Table that uses this ID# to define the relationship. This 2nd Table is used to keep track of the current Shot Count. It only has 5 fields.
_ID# (as mention above)
Current Date (the date each record was created)
Week Ending Date (the next Saturday)
Current Shot Count (either a 0 or 1)
Total Shot Count (a calculated summary of all Current Shots Counts)
In the Main Table I've created a Portal that references the 2nd Table, and displays the most recent record information for Current Shot Count (either a 0 or 1). Unfortunately the other Portal I've created to display the Total Shot Count is not calculating the most current totals, it's calculating ALL totals.
For example, I've created four new records in the 2nd Table.
Record #1: _ID# = 1; Current Date = 09/01/10; Week Ending Date = 09/04/10; Current Shot Count = 1; Total Shot Count calculation = 1)
Record #2 _ID# = 1; Current Date = 09/02/10; Week Ending Date = 09/04/10; Current Shot Count = 0; Total Shot Count calculation = 1)
Record #3: _ID# = 1; Current Date = 09/03/10; Week Ending Date = 09/04/10; Current Shot Count = 1; Total Shot Count calculation = 2)
Record #4: _ID# = 1; Current Date = 09/04/10; Week Ending Date = 09/04/10; Current Shot Count = 0; Total Shot Count calculation = 2)
In this example I have a particular shot that was activated on 09/01, Omitted on 09/02, Re-activate on 09/03, and finally Omitted on 09/04.
When I go back to my Main Table, the Current Shot Count Portal displays a 0... which is correct, since the shot was omitted on 09/04. But the Total Shot Count Portal displays a 2 (it's calculating ALL of the records, instead on only the most current).
Is there a way to make this Total Shot Count Portal only add up the most recent status (using Current Date to sort), with the understanding that no matter how many Records are created with the same _ID#, the final Total for that particular shot can only be a 0 or 1?
In the above example, on the Main Table the Current Shot Count Portal = 0, and the Total Shot Count Portal calculation = 0.
I am not very advanced here, and this might be a very complicated question. I do appreciate any advice you can send my way.
Thank you for your time and consideration.