Counting Grandchild records for a Child to display on the Parent record
I have a tricky issue and I'm unsure where or how to find a solution for it:
We provide annual service contracts (retainers) for customers and then charge them each time we make a visit to their site. This works well for single site customers such as small businesses but the larger multi-site customers prefer an all-in approach.
Traditionally, we have managed this all-in approach by agreeing a contract where we look after say 8 sites for a customer and pre-billing them 24 visits, a 'Bundled' deal in effect which works out at an average of 3 visits per site.
An Excel sheet is then used to log each visit so that when the visits count down to zero, we start charging them for the 25th visit and above.
The way our database is set up currently, if we have 8 'Sites' then they are children of a parent 'Client'. I think it might be an idea to mark this Client as 'Bundled' and then allocate an amount of visits (24).
I then need a fancy way of adding up each visit from the associated 8 sites so that a current 'remaining balance' can be displayed on the Client layout.
Bloggs Co. (list of 8 child sites)
Quantity = 24
Used = 6
Balance = 18
Does the logic in the above seem sound? If yes, any ideas on how I would calc' it all out?