brian.curran

Sub-Summary calculations for billing purposes...

Discussion created by brian.curran on Dec 26, 2012
Latest reply on Jan 19, 2013 by brian.curran

Hi,

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:

 

Clients Table:

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:

 

Tables:

Clients, Sites, Incidents, Bundles

 

Relationships:

Client::__kpClientID = Site::_kfClientID

Client::__kpClientID = Bundles::_kfClientID

Site::__kpSiteID = Incidents::_kfSiteID

 

Any advice would be great, many thanks...

Brian.

Outcomes