Hello Dennis and Karl
I can see that I didn't do a very good job of explaining what's required!
Here goes again:
1. The query must only contain one row per 'employeename'.
2. For the "simple_employees", the query just needs to return that 'cont
rate' value, and the total of 'nhsp ees', and the total of 'pen pay';
3. For the "variable_employees", the query needs to return the latest 'cont
rate' value (i.e. the one with the maximum 'month number' value - in the
same column as the "simple_employees" one-and-only 'cont rate' value), and
the total of 'nhsp ees' and the total of 'pen pay' (again, in the same
columns as for the "simple_employees")
4. In addition for the "variable_employees", the query needs to return the
following columns for each earlier 'cont rate' value: the 'cont rate' value,
the minimum 'month number', the maximum 'month number', the total of 'nhsp
ees', and the total of 'pen pay'.
An example would be helpful. Say tabel [x confirmed] contains the following
records (using ; as field delimeter)
'employeename' ; 'month number' ; 'cont rate' ; 'nhsp ees' ; 'pen pay'
Jack Sparrow ; 122 ; 0.5 ; 2 ; 5
Jack Sparrow ; 123 ; 0.5 ; 2 ; 5
Jack Sparrow ; 124 ; 0.5 ; 2 ; 5
Robin Hood ; 122 ; 0.5 ; 2 ; 5
Robin Hood ; 123 ; 0.5 ; 2 ; 5
Robin Hood ; 124 ; 0.5 ; 2 ; 4
Robin Hood ; 125 ; 0.4 ; 2 ; 5
Robin Hood ; 126 ; 0.4 ; 2 ; 5
James Bond; 123 ; 0.4 ; 2 ; 3
James Bond; 124 ; 0.4 ; 2 ; 3
James Bond; 125 ; 0.5 ; 2 ; 5
James Bond; 126 ; 0.5 ; 4 ; 7
James Bond; 127 ; 0.6 ; 3 ; 5
James Bond; 128 ; 0.6 ; 3 ; 6
The query should return the following fieldnames:
employeename;
final 'cont rate';
total 'nhsp ees';
total 'pen pay';
var1_rate;
var1_min_month
var1_max_month
var1_'nhsp ees'
var1_'pen pay'
var2_rate;
var2_min_month
var2_max_month
var2_'nhsp ees'
var2_'pen pay'
And with the above data, the output should be:
Jack Sparrow ; 0.5 ; 6 ; 15 (plus 10 null/empty fields)
Robin Hood ; 0.4 ; 10 ; 24 ; 0.5 ; 122 ; 124 ; 6 ; 14 (plus 5 null/empty
fields)
James Bond ; 0.6 ; 16 ; 29 ; 0.4 ; 123 ; 124 ; 4 ; 6 ; 0.5 ; 125 ; 126 ; 6 ;
12
Does that help: hope so!!
Thanks again in advance
Les
"KARL DEWEY" wrote in message
news
I has to use multiple queries --
Last_Month --
SELECT [x confirmed].EMPLOYEENAME, Max([x confirmed].[month number]) AS
[MaxOfmonth number]
FROM [x confirmed]
GROUP BY [x confirmed].EMPLOYEENAME;
Last_Month_Cont_Rate --
SELECT [x confirmed].EMPLOYEENAME, [x confirmed].[month number], [x
confirmed].[cont rate]
FROM [x confirmed] INNER JOIN Last_Month ON ([x confirmed].EMPLOYEENAME =
Last_Month.EMPLOYEENAME) AND ([x confirmed].[month number] =
Last_Month.[MaxOfmonth number]);
Last_Month_Cont_RatePLUS --
SELECT [x confirmed].EMPLOYEENAME, [x confirmed].[month number], [x
confirmed].[cont rate], (SELECT SUM(Nz([XX].[nhsp ees], 0) + Nz([XX].[pen
pay], 0)) FROM [x confirmed] AS [XX] WHERE [XX].EMPLOYEENAME = [x
confirmed].EMPLOYEENAME AND [XX].[cont rate] = [x confirmed].[cont rate] )
AS
Total_nhsp_eesANDpen_pay
FROM [x confirmed] INNER JOIN Last_Month_Cont_Rate ON ([x
confirmed].EMPLOYEENAME = Last_Month_Cont_Rate.EMPLOYEENAME) AND ([x
confirmed].[month number] = Last_Month_Cont_Rate.[month number]) AND ([x
confirmed].[cont rate] = Last_Month_Cont_Rate.[cont rate]);
I do not follow what you want in the additional fields as I start counting
stuff in your 3.
I find these fields as a minimum -- MINMon CONT NHSP PEN MAXMon CONT NHSP
PEN in going over your words.
--
Build a little, test a little.
"PayeDoc" wrote:
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
.