calculate sum by commission and end total

[andyhewett]

Any advice will be most greatfully recieved

i am trying to setup a report that will allow me to print a report displaying payment transaction that people have made on a monthly basis. just to give you some background information i have serveral reps that have clients against their own names which they pay for example 100GBP each month on the same date by a standing order. what i would like to do is have the report show this transation and previous payments from them and show me the total paid and a reps commission box showing how much commission they have recieve which should be 20% of the total paid.

Thanks Andy Hewett

 

[SkipVought]

Hi Andy,

Put yourself in my shoes, could you?

Knowing what I know about your database (which is nothing) could you answer my question?

Give us a tad more info, chum!

Skip,

_[red][/red]

 

[andyhewett]

sorry

well my payments table is as follows

Agreement ID (Contract number)
Transaction ID (autonumber so Audit)
Payment (list box ("deposit";"Re-Occuring"))
Amount (Amount Payed)
Payment Method (List box for CC/DC cheque cash or Standing order)
CreditCard Auth Number (Credit clearance number)
Rep Commission (comission rep gets from each transation entered)
Number of Months (number of months we have to recieve the standing order before full payment is achieved)
Standing Order Start Date (when we can start taking payments)

what i want to do it create a report that will show me all the transation between a certain date for each individual Rep. from this report i also want to be able to see how much money they have generated for the company and how much money they get in commission. But the one problem is if we get a deposit of £1000 the system will give the rep 50% or what ever has been stated in the comission field, but we also want to put a cap on the commission at £300 so if the take a £1000 deposit they only get £300 instead of £500.

Each Agreement number is related to a customer which is also related to a Rep.

i was wondering how this would be possible and im open to suggestion.

any more info required please ask.

Thanks Andy Hewett