Very difficult query (?)

Hello

I've been struggling with this all day, and although I think I'm near a
solution (actually I thought that 2 hours ago!) it now involves 4 nested
queries and is 'grinding' horribly (i.e. takes 30-40 seconds to run), and
I'm convinced that there must be a better way to get what I need! I really
hope someone can help with this.

I have a table [x confirmed] which includes the following fields:

'employeename' (text)
'month number' (integer)
'cont rate' (number - percent)
'nhsp ees' (number - currency)
'pen pay' (number - currency)

Each 'employeename' occurs many times in the table, but only once for each
'month number' value. For each 'employeename' value there is usually just
one 'cont rate' value (call these "simple_employees"), but for some
'employeename' values there are 2, or a maximum of 3, different 'cont rate'
values (so a maximum of 2 "variations": call these "variable_employees").

I need a query that will return one row per employee, which will, for each
'employeename':

1. return the latest 'cont rate' value (i.e. the one with the highest 'month
number' value for that 'employeename');

2. return the total value of 'nhsp ees' and 'pen pay' where the 'cont rate'
value = the latest 'cont rate' value described in (1) above;

3. include 10 further columns that will return 'nulls' for the
"simple_employees"; and for the "variable_employees", for each different
'cont rate' value - APART FROM the latest 'cont rate' value described in (1)
above - the query must return the coresponding minimum and maximum 'month
number' values, and the total coresponding values of 'nhsp ees' and 'pen
pay' must be returned - along with the 'cont rate' value. So a
"variable_employee" with 2 different 'cont rate' values will have values in
the first 5 of these 10 columns, and nulls in the last 5: a
"variable_employee" with 3 different 'cont rate' values will have values in
the first 5 of these 10 columns corresponding to their 1st 'cont rate'
variation, and they will have values in the last 5 of these 10 columns
corresponding to their 2nd 'cont rate' variation.

And that's it!

I would be so grateful for some help with this, as my own 'solution' has
become impractical - and isn't right anyway!

In hope.
Many thanks
Leslie Isaacs