Finding the date of last pay rise

B

Bob Quintal

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
Click to expand...
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?
 
J

John

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
 
K

Ken Sheridan

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
 
J

John

Hi Bob

Thanks for that.

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

Regards

Bob Quintal said:
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
Click to expand...
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?
Click to expand...
 
J

John

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 said:
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
Click to expand...
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?
Click to expand...
 
J

John

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 said:
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 said:
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
Click to expand...
Click to expand...
 
K

Ken Sheridan

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 said:
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 said:
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
Click to expand...
Click to expand...
 
K

Ken Sheridan

Our last posts crossed in the ether.

KWS

John said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Quintal

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?
Click to expand...
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 said:
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
Click to expand...
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?
Click to expand...
Click to expand...
 

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

Access query 5
Issues with Left Join 1
Comparison Query - How to write it! 6
Date questions in query 12
Two Table Query 4
'Enter parameter value' message 1
Use the Second Max Value in a Formula???? 4
Total Query 3

Top