Top 2 records from related table

  • 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'm trying to do a query where I get all the records from
the staff table (the one side) and only the Top 2 records
from the compensation (many side). The object here is a
report that lists, for each individual on staff, their
last two salaries (which would correspond to current and
last salary).

I have tried various combinations of Totals queries, Top
2 queries, etc. but can't seem to get it right.

Thanks for any help!

Steve
 
You might try a query whose SQL looks something like this:

SELECT
[Staff].*,
[Compensation].*
FROM
[Staff]
INNER JOIN
[Compensation]
ON
[Staff].[Staff ID] = [Compensation].[Staff ID]
WHERE
[Compensation].[Date of Raise] In
(SELECT TOP 2
[Self].[Date of Raise]
FROM
[Compensation] AS [Self]
WHERE
[Self].[Staff ID] = [Staff].[Staff ID]
ORDER BY
[Self].[Date of Raise] DESC)
 
Wow -- that's complicated but IT WORKS!!!

Thanks so much for your help :)

Steve
-----Original Message-----
You might try a query whose SQL looks something like this:

SELECT
[Staff].*,
[Compensation].*
FROM
[Staff]
INNER JOIN
[Compensation]
ON
[Staff].[Staff ID] = [Compensation].[Staff ID]
WHERE
[Compensation].[Date of Raise] In
(SELECT TOP 2
[Self].[Date of Raise]
FROM
[Compensation] AS [Self]
WHERE
[Self].[Staff ID] = [Staff].[Staff ID]
ORDER BY
[Self].[Date of Raise] DESC)

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'm trying to do a query where I get all the records from
the staff table (the one side) and only the Top 2 records
from the compensation (many side). The object here is a
report that lists, for each individual on staff, their
last two salaries (which would correspond to current and
last salary).

I have tried various combinations of Totals queries, Top
2 queries, etc. but can't seem to get it right.

Thanks for any help!

Steve
Click to expand...


.
Click to expand...
 
Back
Top