Very difficult query (?)

P

PayeDoc

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
 
D

Dennis

PayeDoc,

I've read you question and am quite confused.

You might consider this approach:

Use and Append and Group by query to create a new table that has one rcd per
employee name. The group by would also allow you group my employee name /
number and create Minium Cont Rate and Maximum Cont Rate fields in this new
table.

In the new table, you could create compares the Min and Max Cont Rate. If
they are the same, set this field to true. If they are different set this
field to false.

This table now gives you 1 rcd per empoyee name, their Minimum and Max Cont
Rate, and if they are a simple or varialbe employee.

I don't understand the rest of your question, specifically

1. Return the total of value of "nhsp ees" and "pen pay". Do you want to
add "nhsp ees" and "pen pay" together or to you want the total of "nhsp ees"
and the total of "pen pay" for all record for the given employee that have
the highest cont rate?

2. I don't understand paragraph 3 at all other than the first five column
will be have data if the employee is variable while the last 5 will be null.
The reverse is true if the employee is simple.

However, I don't understand what data will appear in the first 5 or the last
five columns.

I can see that you want the min and max cont rate for varialbe employees.
You also want the cont rate. Is the max cont rate and the cont rate the
same? If so, why do you need it twice?

What total corresponding values for "nhsp ees" and "pen pay" do you want?
The total for ALL values for this employee or just the total value for the
records where the rcd's cont rate = max cont rate?

As for last 5 columns, since there is only one rate for a simple employee
why do you have a min and max = they will be the same.

You last paragraphs states "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."

Are you trying to say that the values for the 1st cont rate goes in the
first five columns, ,the value for the 2nd cont rate goes in the next five
column, and if there is a 3rd and highest cont rate, thoses values are not
included because you only have two sets of totals?

I'm sorry I did not understand your question. I tried reading many
different times and am still confused. To be honest, I'n not sure what it is
you are trying to do.

That might be part of the problem. If you do not have a clear definition of
what you are trying to accomplish, you will never accomplish it.

Dennis
 
K

KARL DEWEY

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 said:
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




.
Click to expand...
 
L

Leslie Isaacs

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 said:
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 said:
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




.
Click to expand...
Click to expand...
 
D

Dennis

Leslie,

Your example and specification do not match.

You state:

1. One line per employee
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'.

James Bond has three "cont rate". However, the report only shows the lowest
and highest "cont rate". It does not show the middle value, which
contradicts your stated requirement number 4.

What's the story on this?


Dennis
 
D

Dennis

Leslie,

I see where the most current is printed first and then the other two are
printed in the Var1 and Var2 columns. So never mind my earlier comment.

Are you saying that there will NEVER EVER be more than three rates?

Also, does it matter which rate is in Var1 and which rate is in Var2?

Dennis
 
D

Dennis

Leslie,

I've have taken your example and reformatted it with ";". If someone wants
to take a look at the report, they can copy the lines from below and paste it
into Word or someother work processer. Then can then change the ; to tab
marks. It makes it much easier to look at the report.


The sample did make it clearer. However, I'm not quite sure how to go about
it.

My guess is you want to use a Group by Query to group on Employee and Cont
Rate. You will want to take the Min Month and Max Month and total Nhsp ees
and Pen Pay. You will output this to a temporary table.

I think from there you want to do a Cross Tab query agains your temporary
table, but I'm not sure. I've never done one before, but I've read about it.

Also, how do you figure which months go in Var1 columns and which months go
in Var2 column? I did not see an explanation for that.

Sorry I could not be of more help. Let me think about it some more.

Good luck.

Dennis
--
Dennis


Leslie Isaacs said:
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 said:
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 said:
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




.
Click to expand...
Click to expand...


.
Click to expand...
 
D

Dennis

Leslie,

One other approach might to be create five queries that generate each piece
of the line

1. One that groups by all of the data as described above.
2. One that reads the temp table and creates a table with the first three
columns.
3. One that reads the temp table and creates a table for the 2nd column set
4. One that reads the temp table and creates a table for the last columns

A fifth would join the last three tables into one, resulting in one line per
employee with all of the data on it.

Dennis
 
L

Leslie Isaacs

Hello Dennis

Very many thanks for your persistence with this. In answer to your
questions:

There will never be more than 3 'cont rate' values for any 'employeename'.
Var1 should represent the earliest 'cont rate' value, and Var2 should
represent the latest 'cont rate' value (unless there is only one 'cont rate'
value, in which case the Var1 columns must be null/blank).
I should also add that the query will ultimately be used with an OutputTo
macro command, so that an excel spreadsheet of the results can be emailed
out. It's this excel spreadsheet that MUST be in a pre-defined format -
which is what's dictating the output of the query. In view of this, I don't
think a cross-tab query would work.

I think your suggested "other approach" is very similar to what I had been
trying, so it's reassuring to know that I'm probably on the right lines
(because I'd seriousdly begun to doubt it!).

I'll therefore carry on, and get back to you all here if I get completely
stuck.

Many thanks again
Les
 
D

Dennis

Leslie,

You might want to take a look at the cross tab query. I've never used it,
but I think it does what you are trying to do here.

Good luck.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Why is my query asking for a paramater? 6
Help with crosstab query 2
Difficult query 7
Seagate's Seek Error Rate, Raw Read Error Rate, & Hardware ECC Recovered SMART attributes 0
Tip: Table design and query for bracketed lookup 12
Rating system: Query to get a rate for each entry found. 3
Parameter Problem 4
tax computation in Access query 3

Top