"John" <John@nospam.infovis.co.uk> wrote in
news:u6p1qNIUIHA.5264@TK2MSFTNGP02.phx.gbl:

> Hi
>
> I have a Jobs table with Staff ID, Job Rate and Job Date. Staff
> start at a rate and their jobs from that day onwards have that
> rate. Then they get a raise and this is reflected in their
> subsequent jobs and so on.
>
> I need to extract from the above table a result set which has
> Staff ID, Max of the their Rates (i.e. current salary of the
> staff), First Job's date on that Max Rate (i.e. date of last pay
> rise). How can I achieve this using queries?
>
> Thanks
>
> Regards
>
You need to use a subquery to first return the max() value related
to the employee ID, then use that to get the other relevand data.

SELECT M.[Staff ID], M.[Job Rate], M.[Job Start Date]
FROM Jobs Alias M
WHERE M.[Job Rate] =
(SELECT max([Job Rate]) as CurrentRate
FROM Jobs Alias S
WHERE M.[Staff ID] = S.[Staff ID])
AND M.[Staff ID] = [S. Job ID]

You can build the subquery first, test it, and then paste the SQL
into the criteria row of the Main Query.

But Is your way the right way? What if an employee gets a pay
decrease? ( it does happen)

Would it not be better to get the most recent Job date and lookup
the salary?

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

"John" <John@nospam.infovis.co.uk> wrote in
news:u6p1qNIUIHA.5264@TK2MSFTNGP02.phx.gbl:

> Hi
>
> I have a Jobs table with Staff ID, Job Rate and Job Date. Staff
> start at a rate and their jobs from that day onwards have that
> rate. Then they get a raise and this is reflected in their
> subsequent jobs and so on.
>
> I need to extract from the above table a result set which has
> Staff ID, Max of the their Rates (i.e. current salary of the
> staff), First Job's date on that Max Rate (i.e. date of last pay
> rise). How can I achieve this using queries?
>
> Thanks
>
> Regards
>
You need to use a subquery to first return the max() value related
to the employee ID, then use that to get the other relevand data.

SELECT M.[Staff ID], M.[Job Rate], M.[Job Start Date]
FROM Jobs Alias M
WHERE M.[Job Rate] =
(SELECT max([Job Rate]) as CurrentRate
FROM Jobs Alias S
WHERE M.[Staff ID] = S.[Staff ID])
AND M.[Staff ID] = [S. Job ID]

You can build the subquery first, test it, and then paste the SQL
into the criteria row of the Main Query.

But Is your way the right way? What if an employee gets a pay
decrease? ( it does happen)

Would it not be better to get the most recent Job date and lookup
the salary?

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Hi

I have a Jobs table with Staff ID, Job Rate and Job Date. Staff start at a
rate and their jobs from that day onwards have that rate. Then they get a
raise and this is reflected in their subsequent jobs and so on.

I need to extract from the above table a result set which has Staff ID, Max
of the their Rates (i.e. current salary of the staff), First Job's date on
that Max Rate (i.e. date of last pay rise). How can I achieve this using
queries?

Thanks

Regards

Use subqueries to return the MIN Job Date for the MAX Job Rate for the
current Staff ID, e.g.

SELECT [Staff ID], MAX([Job Rate]) AS [Current Rate],
(SELECT MIN([Job Date])
FROM Jobs AS J2
WHERE J2.[Staff ID] = J1.[Staff ID]
AND J2.[Job Rate] =
(SELECT MAX([Job Rate])
FROM Jobs AS J3
WHERE J3.[Staff ID] = J1.[Staff ID]))
AS [Date of Last Pay Rise]
FROM Jobs AS J1;

Ken Sheridan
Stafford, England

"John" wrote:

> Hi
>
> I have a Jobs table with Staff ID, Job Rate and Job Date. Staff start at a
> rate and their jobs from that day onwards have that rate. Then they get a
> raise and this is reflected in their subsequent jobs and so on.
>
> I need to extract from the above table a result set which has Staff ID, Max
> of the their Rates (i.e. current salary of the staff), First Job's date on
> that Max Rate (i.e. date of last pay rise). How can I achieve this using
> queries?
>
> Thanks
>
> Regards
>
>
>

Use subqueries to return the MIN Job Date for the MAX Job Rate for the
current Staff ID, e.g.

SELECT [Staff ID], MAX([Job Rate]) AS [Current Rate],
(SELECT MIN([Job Date])
FROM Jobs AS J2
WHERE J2.[Staff ID] = J1.[Staff ID]
AND J2.[Job Rate] =
(SELECT MAX([Job Rate])
FROM Jobs AS J3
WHERE J3.[Staff ID] = J1.[Staff ID]))
AS [Date of Last Pay Rise]
FROM Jobs AS J1;

Ken Sheridan
Stafford, England

"John" wrote:

> Hi
>
> I have a Jobs table with Staff ID, Job Rate and Job Date. Staff start at a
> rate and their jobs from that day onwards have that rate. Then they get a
> raise and this is reflected in their subsequent jobs and so on.
>
> I need to extract from the above table a result set which has Staff ID, Max
> of the their Rates (i.e. current salary of the staff), First Job's date on
> that Max Rate (i.e. date of last pay rise). How can I achieve this using
> queries?
>
> Thanks
>
> Regards
>
>
>

Hi Bob

Thanks for that.

No staff do not get pay decrease, they get fined or fired. :)

Regards

"Bob Quintal" <rquintal@sPAmpatico.ca> wrote in message
news:Xns9A1D7B7653698BQuintal@66.150.105.47...
> "John" <John@nospam.infovis.co.uk> wrote in
> news:u6p1qNIUIHA.5264@TK2MSFTNGP02.phx.gbl:
>
>> Hi
>>
>> I have a Jobs table with Staff ID, Job Rate and Job Date. Staff
>> start at a rate and their jobs from that day onwards have that
>> rate. Then they get a raise and this is reflected in their
>> subsequent jobs and so on.
>>
>> I need to extract from the above table a result set which has
>> Staff ID, Max of the their Rates (i.e. current salary of the
>> staff), First Job's date on that Max Rate (i.e. date of last pay
>> rise). How can I achieve this using queries?
>>
>> Thanks
>>
>> Regards
>>
> You need to use a subquery to first return the max() value related
> to the employee ID, then use that to get the other relevand data.
>
> SELECT M.[Staff ID], M.[Job Rate], M.[Job Start Date]
> FROM Jobs Alias M
> WHERE M.[Job Rate] =
> (SELECT max([Job Rate]) as CurrentRate
> FROM Jobs Alias S
> WHERE M.[Staff ID] = S.[Staff ID])
> AND M.[Staff ID] = [S. Job ID]
>
> You can build the subquery first, test it, and then paste the SQL
> into the criteria row of the Main Query.
>
> But Is your way the right way? What if an employee gets a pay
> decrease? ( it does happen)
>
> Would it not be better to get the most recent Job date and lookup
> the salary?
>
> --
> Bob Quintal
>
> PA is y I've altered my email address.
>
> --
> Posted via a free Usenet account from http://www.teranews.com
>

Hi Bob

Thanks for that.

No staff do not get pay decrease, they get fined or fired. :)

Regards

"Bob Quintal" <rquintal@sPAmpatico.ca> wrote in message
news:Xns9A1D7B7653698BQuintal@66.150.105.47...
> "John" <John@nospam.infovis.co.uk> wrote in
> news:u6p1qNIUIHA.5264@TK2MSFTNGP02.phx.gbl:
>
>> Hi
>>
>> I have a Jobs table with Staff ID, Job Rate and Job Date. Staff
>> start at a rate and their jobs from that day onwards have that
>> rate. Then they get a raise and this is reflected in their
>> subsequent jobs and so on.
>>
>> I need to extract from the above table a result set which has
>> Staff ID, Max of the their Rates (i.e. current salary of the
>> staff), First Job's date on that Max Rate (i.e. date of last pay
>> rise). How can I achieve this using queries?
>>
>> Thanks
>>
>> Regards
>>
> You need to use a subquery to first return the max() value related
> to the employee ID, then use that to get the other relevand data.
>
> SELECT M.[Staff ID], M.[Job Rate], M.[Job Start Date]
> FROM Jobs Alias M
> WHERE M.[Job Rate] =
> (SELECT max([Job Rate]) as CurrentRate
> FROM Jobs Alias S
> WHERE M.[Staff ID] = S.[Staff ID])
> AND M.[Staff ID] = [S. Job ID]
>
> You can build the subquery first, test it, and then paste the SQL
> into the criteria row of the Main Query.
>
> But Is your way the right way? What if an employee gets a pay
> decrease? ( it does happen)
>
> Would it not be better to get the most recent Job date and lookup
> the salary?
>
> --
> Bob Quintal
>
> PA is y I've altered my email address.
>
> --
> Posted via a free Usenet account from http://www.teranews.com
>

Hi Bob

Looks like I have not understood the sql well. It is not returning any rows.
Here is what I tried.

SELECT M.[Staff ID], M.[Rate], M.[Date]
FROM [Jobs] as M
WHERE M.[Rate] =
(SELECT max([Rate]) as CurrentRate
FROM [Jobs] As S
WHERE M.[Staff ID] = S.[Staff ID])
AND M.[Job ID] = S.[Job ID]

Any ideas?

Thanks

Regards


"Bob Quintal" <rquintal@sPAmpatico.ca> wrote in message
news:Xns9A1D7B7653698BQuintal@66.150.105.47...
> "John" <John@nospam.infovis.co.uk> wrote in
> news:u6p1qNIUIHA.5264@TK2MSFTNGP02.phx.gbl:
>
>> Hi
>>
>> I have a Jobs table with Staff ID, Job Rate and Job Date. Staff
>> start at a rate and their jobs from that day onwards have that
>> rate. Then they get a raise and this is reflected in their
>> subsequent jobs and so on.
>>
>> I need to extract from the above table a result set which has
>> Staff ID, Max of the their Rates (i.e. current salary of the
>> staff), First Job's date on that Max Rate (i.e. date of last pay
>> rise). How can I achieve this using queries?
>>
>> Thanks
>>
>> Regards
>>
> You need to use a subquery to first return the max() value related
> to the employee ID, then use that to get the other relevand data.
>
> SELECT M.[Staff ID], M.[Job Rate], M.[Job Start Date]
> FROM Jobs Alias M
> WHERE M.[Job Rate] =
> (SELECT max([Job Rate]) as CurrentRate
> FROM Jobs Alias S
> WHERE M.[Staff ID] = S.[Staff ID])
> AND M.[Staff ID] = [S. Job ID]
>
> You can build the subquery first, test it, and then paste the SQL
> into the criteria row of the Main Query.
>
> But Is your way the right way? What if an employee gets a pay
> decrease? ( it does happen)
>
> Would it not be better to get the most recent Job date and lookup
> the salary?
>
> --
> Bob Quintal
>
> PA is y I've altered my email address.
>
> --
> Posted via a free Usenet account from http://www.teranews.com
>

Hi Bob

Looks like I have not understood the sql well. It is not returning any rows.
Here is what I tried.

SELECT M.[Staff ID], M.[Rate], M.[Date]
FROM [Jobs] as M
WHERE M.[Rate] =
(SELECT max([Rate]) as CurrentRate
FROM [Jobs] As S
WHERE M.[Staff ID] = S.[Staff ID])
AND M.[Job ID] = S.[Job ID]

Any ideas?

Thanks

Regards


"Bob Quintal" <rquintal@sPAmpatico.ca> wrote in message
news:Xns9A1D7B7653698BQuintal@66.150.105.47...
> "John" <John@nospam.infovis.co.uk> wrote in
> news:u6p1qNIUIHA.5264@TK2MSFTNGP02.phx.gbl:
>
>> Hi
>>
>> I have a Jobs table with Staff ID, Job Rate and Job Date. Staff
>> start at a rate and their jobs from that day onwards have that
>> rate. Then they get a raise and this is reflected in their
>> subsequent jobs and so on.
>>
>> I need to extract from the above table a result set which has
>> Staff ID, Max of the their Rates (i.e. current salary of the
>> staff), First Job's date on that Max Rate (i.e. date of last pay
>> rise). How can I achieve this using queries?
>>
>> Thanks
>>
>> Regards
>>
> You need to use a subquery to first return the max() value related
> to the employee ID, then use that to get the other relevand data.
>
> SELECT M.[Staff ID], M.[Job Rate], M.[Job Start Date]
> FROM Jobs Alias M
> WHERE M.[Job Rate] =
> (SELECT max([Job Rate]) as CurrentRate
> FROM Jobs Alias S
> WHERE M.[Staff ID] = S.[Staff ID])
> AND M.[Staff ID] = [S. Job ID]
>
> You can build the subquery first, test it, and then paste the SQL
> into the criteria row of the Main Query.
>
> But Is your way the right way? What if an employee gets a pay
> decrease? ( it does happen)
>
> Would it not be better to get the most recent Job date and lookup
> the salary?
>
> --
> Bob Quintal
>
> PA is y I've altered my email address.
>
> --
> Posted via a free Usenet account from http://www.teranews.com
>

Hi Ken

I have done below using the exact table/field names.

SELECT [Staff ID], MAX([Rate]) AS [Current Rate],
(SELECT MIN([Date])
FROM [Staff Bookings] AS J2
WHERE J2.[Staff ID] = J1.[Staff ID]
AND J2.[Rate] =
(SELECT MAX([Rate])
FROM [Staff Bookings] AS J3
WHERE J3.[Staff ID] = J1.[Staff ID]))
AS [Date of Last Pay Rise]
FROM [Staff Bookings] AS J1;

I am getting the error "You tried to execute a query that does not include
the specified expression 'Staff ID' as part of and aggregate function.

Thanks

Regards

"Ken Sheridan" <KenSheridan@discussions.microsoft.com> wrote in message
news:0434533D-335B-471B-85A6-BD1E91EB3F51@microsoft.com...
> Use subqueries to return the MIN Job Date for the MAX Job Rate for the
> current Staff ID, e.g.
>
> SELECT [Staff ID], MAX([Job Rate]) AS [Current Rate],
> (SELECT MIN([Job Date])
> FROM Jobs AS J2
> WHERE J2.[Staff ID] = J1.[Staff ID]
> AND J2.[Job Rate] =
> (SELECT MAX([Job Rate])
> FROM Jobs AS J3
> WHERE J3.[Staff ID] = J1.[Staff ID]))
> AS [Date of Last Pay Rise]
> FROM Jobs AS J1;
>
> Ken Sheridan
> Stafford, England
>
> "John" wrote:
>
>> Hi
>>
>> I have a Jobs table with Staff ID, Job Rate and Job Date. Staff start at
>> a
>> rate and their jobs from that day onwards have that rate. Then they get a
>> raise and this is reflected in their subsequent jobs and so on.
>>
>> I need to extract from the above table a result set which has Staff ID,
>> Max
>> of the their Rates (i.e. current salary of the staff), First Job's date
>> on
>> that Max Rate (i.e. date of last pay rise). How can I achieve this using
>> queries?
>>
>> Thanks
>>
>> Regards
>>
>>
>>
>