AnsweredAssumed Answered

Summarizing data from only the related records in a found set

Question asked by JoeBrillhart on Feb 7, 2011
Latest reply on Feb 12, 2011 by JoeBrillhart


Summarizing data from only the related records in a found set


I have two tables that are related.  The first table is Personnel Data.  The second table is Parking Violations.  The Personnel Data table is related to the Parking Violations table by the LicensePlate field.  For each Personnel Data record there could be many parking violations (this is a service business in New York City).  I have a third table with only one record that is called Main, where I display YTD data based on the total number of violations, average cost, etc.  Main is related to the other two tables by a field called BranchNumber. On the layout for Main, I also have a section where the user can enter two dates and search for violations that occur between that date range.  I do that through a button script and it works, except that I want to display the found set from the perspective of the Personnel Data so that in table view, the Personnel Records will show one line, and summarize the charges, number of violations, etc for each person that occurred during the date range of the search.  I have been unsuccessful in doing this.  I have no problem doing it for the entire set of records in the database, but when I perform a search and only want the summary data for a slice in time, I still get the summary of ALL the related records instead of the few records in Parking Violations that are related to each Personnel Data record that occur as a result of the search.

For example:  Jim Doe has a total of 25 violations year to date.  When I display all the data sorted by number of violations, Jim's 25 violations show up perfectly as do the total charges for his violations.  When I perform a find on the violations based on a specific date range, Jim only has 3 violations.  In the table view layout I am using (based on Personnel Data table), I need to display Jim Doe, with another field showing 3 violations, another field showing the cost summary of his three violations.

I am probably suffering from a brain freeze and missing a simple elegant solution.  Can anyone please help?

Thank you,