A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Very difficult query (?)



 
 
Thread Tools Display Modes
  #1  
Old May 28th, 2010, 05:32 PM posted to microsoft.public.access.queries
PayeDoc
external usenet poster
 
Posts: 103
Default 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




  #2  
Old May 28th, 2010, 06:55 PM posted to microsoft.public.access.queries
Dennis
external usenet poster
 
Posts: 1,222
Default Very difficult query (?)

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


  #3  
Old May 28th, 2010, 08:50 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Very difficult query (?)

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




.

  #4  
Old May 29th, 2010, 04:15 PM posted to microsoft.public.access.queries
Leslie Isaacs[_2_]
external usenet poster
 
Posts: 85
Default Very difficult query (?)

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




.



  #5  
Old May 30th, 2010, 05:23 AM posted to microsoft.public.access.queries
Dennis
external usenet poster
 
Posts: 1,222
Default Very difficult query (?)

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
  #6  
Old May 30th, 2010, 05:26 AM posted to microsoft.public.access.queries
Dennis
external usenet poster
 
Posts: 1,222
Default Very difficult query (?)

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
  #7  
Old May 30th, 2010, 05:45 AM posted to microsoft.public.access.queries
Dennis
external usenet poster
 
Posts: 1,222
Default Very difficult query (?)

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" wrote:

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




.



.

  #8  
Old May 30th, 2010, 05:50 AM posted to microsoft.public.access.queries
Dennis
external usenet poster
 
Posts: 1,222
Default Very difficult query (?)


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
  #9  
Old May 30th, 2010, 01:01 PM posted to microsoft.public.access.queries
Leslie Isaacs[_2_]
external usenet poster
 
Posts: 85
Default Very difficult query (?)

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


"Dennis" wrote in message
...

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



  #10  
Old May 30th, 2010, 05:26 PM posted to microsoft.public.access.queries
Dennis
external usenet poster
 
Posts: 1,222
Default Very difficult query (?)

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.


--
Dennis


"Leslie Isaacs" wrote:

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


"Dennis" wrote in message
...

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



.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:50 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.