2 Tiered Commisions Formula

Hiya,

I'm Looking for help with a formula used to calculate commisions.

The problem is that the commisions are 2 tiered depending on type of product sold (Low end or high end)
Low end products are identifed by the fact that their stockcode starts with either C,F,G,H,J,T eg: F-4050
High end products are identified by the fact that their stockcode starts with either A,E,K,w eg: E-2074

First commision is determined by summing the ValuetoDate column for each AccountCode (Low end and highend included)
The same AccountCode can appear multiple times in the table.
This total is then compared to a commisions table with the following criteria....see table1

Low end product valuetoDate are summed to Give lowend Total

Low end commision is then = lowend total x low end commision %

Second commision is also determined by summing the ValuetoDate column for each AccountCode (Low end and highend included)
This same total is then compared to a commisions table with the following cirteria....see table2

High end product valuetoDate are summed to yield highend Total

High end commision is = highend total x high end commision %


The 2 commisions are then added together to give a total commison per Accountcode..

can anyone help me with the forumala in either access or excel or SQL.
I have attached some example data containing info on 2 accountcodes

Any help greatlty appretiated..

Ray


Table1

Total Sales Value Commission %
0.00 - 300 0%
301 - 1000 5%
1001 - 2000 7.5%
2001 - 3500 10%
3501 - 4500 12%
4501 - 7500 15%
7501+ 20%



Table2

Total Sales Value Commission %
0.00 - 300 0%
300 - 1000 5.0%
1001+ 7.5%