I'm hoping somebody can help me figure this problem out, a 'Client' can have several 'Sites' associated to it and these can each have many 'Incidents'. A sub-summary report groups several Incidents under a Site and several Sites are grouped together under a Client as shown below.
Bloggs UK (Client)
Bloggs England (Site)
17/04/12 1.25 hours
21/09/12 1 hour
Bloggs Scotland (Site)
16/03/12 1.5 hours
18/10/12 2.5 hours
21/11/12 1 hour
When Incidents are entered into the table, we log the Date, Time and number of Hours spent at the Site. A Calc field in the Client table adds up all the Hours for each of the Sites related to the Client. For example:
Bloggs UK = 7.25 hours
Sometimes, a Client might pre-pay a certain number of hours or we may include some in the annual contract. This is logged in a table called 'Bundles' so Bloggs UK for example, might have prepaid 10 hours at the start of the contract. Another Calc field would therefore state that they have a positive balance of 1.75 hours remaining before we start to bill them for any hourly work.
The sub-summary report above is used by the Accounts department for billing so I want the report to show that Bloggs UK is not to be billed until their 'Balance' calculation field goes into a negative amount.
Can anyone help me devise a method to do this on a row by row basis? For example:
Bloggs Uk prepays 10 hours
17th - 1.25 = 8.75 balance
21st - 1 = 7.75
16th - 1.5 = 6.25
18th - 2.5 = 3.75
21st - 1 = 2.75
The tables and relationships are currently set up as shown below:
Clients, Sites, Incidents, Bundles
Client::__kpClientID = Site::_kfClientID
Client::__kpClientID = Bundles::_kfClientID
Site::__kpSiteID = Incidents::_kfSiteID
Any advice would be great, many thanks...