Finding the date of last pay rise

"John" wrote in
:

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

Hi Bob

Thanks for that.

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

Regards

"Bob Quintal" wrote in message
...
"John" wrote in
:

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" wrote in message
...
"John" wrote in
:

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" wrote in message
...
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

Oops! Missed the GROUP BY Clause:

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
GROUP BY [Staff ID];

Ken Sheridan
Stafford, England

"Ken Sheridan" wrote:

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

Our last posts crossed in the ether.

KWS

"John" wrote:

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" wrote in message
...
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

"John" wrote in
:

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?

The last AND is an editorial error.

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]);


Thanks

Regards


"Bob Quintal" wrote in message
...
"John" wrote in
:

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






--
Bob Quintal

PA is y I've altered my email address.

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