Fairly simple calculation (that i cannot do!)

[plusone]

Hello.

Please see attached picture for an idea on what i'm talking about. I need to achieve the following:

I have a query and it gives a total policy commission for each policy an agent sells. There are several agents under one commission statement.

For example

agent ref 1, policy 1, information, commission amount
agent ref 1, policy 2, information, commission amount
agent ref 1, policy 3, information, commission amount
agent ref 2, policy 1, information, commission amount
agent ref 2, policy 2, information, commission amount
agent ref 2, policy 3, information, commission amount
agent ref 2, policy 4, information, commission amount
agent ref 3, policy 1, information, commission amount
etc...

What i need access to do is add a total for each agent before it shows the next agent, so the above example would change to:

agent ref 1, policy 1, information, commission amount
agent ref 1, policy 2, information, commission amount
agent ref 1, policy 3, information, commission amount
Total: commission total
agent ref 2, policy 1, information, commission amount
agent ref 2, policy 2, information, commission amount
agent ref 2, policy 3, information, commission amount
agent ref 2, policy 4, information, commission amount
Total: commission total
agent ref 3, policy 1, information, commission amount
etc...

I hope that this makes sense, and i know its probably very simple but im a n00b! so any help would be much appreciated.

Kind Regards
Darren

 

[Smart]

try a query like this

select agent,

SELECT yourTable.agent, Sum(yourTable.Commissionamount) AS SumOfcommissionamount,
FROM Table2
GROUP BY yourtable.agent;