Last-n in totals query grid

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I have a simple HR database with two tables. One is a
staff table and one is a compensation table. The staff
tables is related to the compensation table in a one to
many relationship. Each staff record can have multiple
records in the compensation table, which tracks date of
raises, amount of raise, current annual salary, etc. A
new compensation record is created whenever there's a
raise for the employee.

I can do a totals query to get, for each staff person,
the last record from the related Compensation table's
current annual salary field, using Last in the total row.

Is there any way to get the "Next-to-Last" record in
another column in the query grid, so that a report based
on the query can show two columns: Current Salary and
Last Salary?

Thanks,

Steve
 
Steve said:
I have a simple HR database with two tables. One is a
staff table and one is a compensation table. The staff
tables is related to the compensation table in a one to
many relationship. Each staff record can have multiple
records in the compensation table, which tracks date of
raises, amount of raise, current annual salary, etc. A
new compensation record is created whenever there's a
raise for the employee.

I can do a totals query to get, for each staff person,
the last record from the related Compensation table's
current annual salary field, using Last in the total row.

Is there any way to get the "Next-to-Last" record in
another column in the query grid, so that a report based
on the query can show two columns: Current Salary and
Last Salary?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Min() and Max() are better functions than First() and Last(). Here is
an example ("How to pull the second highest value from a table using a
query") I took out of a query example db (from MS). You can modify to
your requirements.

SELECT Min(qryTop2.Quantity) AS MinOfQuantity
FROM qryTop2;

qryTop2:
SELECT DISTINCT TOP 2 [Order Details].Quantity
FROM [Order Details]
ORDER BY [Order Details].Quantity DESC;

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQGOMC4echKqOuFEgEQJzRQCg/zk8+lwVrIepIjDE+QlnWomzfoIAnAvY
ZPqNas4TpHJ9FhGuxWlTJa3F
=o+Rf
-----END PGP SIGNATURE-----
 
I've attempted to apply your example to my case, but with
it I just get the top two values from the recordset:

[SELECT TOP 2 [Staff Table].LastName, [Staff Table].
FirstName, Compensation.CompID, Compensation.CurrCompYr,
Compensation.CurrCompHr
FROM [Staff Table] LEFT JOIN Compensation ON [Staff Table].
StaffID = Compensation.StaffID
ORDER BY Compensation.CompID DESC;]


WhatI want, however, are ALL the Names from the Staff side
of the query and only the the top two related Salary fields
from the Compensation side of the query.

Anybody got any ideas of how to get this result?

Steve

-----Original Message-----
Steve said:
I have a simple HR database with two tables. One is a
staff table and one is a compensation table. The staff
tables is related to the compensation table in a one to
many relationship. Each staff record can have multiple
records in the compensation table, which tracks date of
raises, amount of raise, current annual salary, etc. A
new compensation record is created whenever there's a
raise for the employee.

I can do a totals query to get, for each staff person,
the last record from the related Compensation table's
current annual salary field, using Last in the total row.

Is there any way to get the "Next-to-Last" record in
another column in the query grid, so that a report based
on the query can show two columns: Current Salary and
Last Salary?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Min() and Max() are better functions than First() and Last(). Here is
an example ("How to pull the second highest value from a table using a
query") I took out of a query example db (from MS). You can modify to
your requirements.

SELECT Min(qryTop2.Quantity) AS MinOfQuantity
FROM qryTop2;

qryTop2:
SELECT DISTINCT TOP 2 [Order Details].Quantity
FROM [Order Details]
ORDER BY [Order Details].Quantity DESC;

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQGOMC4echKqOuFEgEQJzRQCg/zk8+lwVrIepIjDE+QlnWomzfo IAnAvY
ZPqNas4TpHJ9FhGuxWlTJa3F
=o+Rf
-----END PGP SIGNATURE-----

.
Click to expand...
 
Steve said:
I've attempted to apply your example to my case, but with
it I just get the top two values from the recordset:

[SELECT TOP 2 [Staff Table].LastName, [Staff Table].
FirstName, Compensation.CompID, Compensation.CurrCompYr,
Compensation.CurrCompHr
FROM [Staff Table] LEFT JOIN Compensation ON [Staff Table].
StaffID = Compensation.StaffID
ORDER BY Compensation.CompID DESC;]


WhatI want, however, are ALL the Names from the Staff side
of the query and only the the top two related Salary fields
from the Compensation side of the query.

Anybody got any ideas of how to get this result?

Steve


-----Original Message-----
Steve wrote:


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

< snip previous reply >

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ah... A horse of a different color (which means I didn't read the
original post very closely). We might include the TOP 2 concept w/ a
different query. I couldn't think of anything except the below, which
gets the DATES of the last two salary bumps. Couldn't figure out a way
to get the previous/current SALARIES on the same line. Maybe you can
think of something.

SELECT StaffID,
Max(SalaryDate) As CurrentSalaryDate,
Min(SalaryDate) As PreviousSalaryDate
FROM Compensation As C2
WHERE CompID IN (SELECT TOP 2 CompID FROM Compensation WHERE StaffID =
C2.StaffID ORDER BY SalaryDate DESC)
GROUP BY StaffID
ORDER BY 1, 2 DESC

SalaryDate (or whatever you named it) column needs to be indexed.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQGZKcoechKqOuFEgEQIuMQCfZr19X+mTTCHTeyFNZCLLyHSRI6sAn1d+
9/1zoGmRMNjN51FU3wtaJV3b
=qn3c
-----END PGP SIGNATURE-----
 
Back
Top