Get max # record from related table

[Steve Sherman]

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
raise, amount of raise, current annual salary, etc. A new
compensation record is created whenever there?s a raise for
the employee. Each compensation record has a primary key
field called ?CompID?

I am trying to do a query which lists every employee record
from the staff side and only the most current record (which
will have the highest/max CompID number) of the
compensation table. [And I could therefore create a report
which lists on one line each employee and some fields from
the Staff table as well as the current annual salary, etc.
from the Compensation table]

How can I set up the query grid to accomplish this?

Thanks,

Steve Sherman

 

[Nikos Yannacopoulos]

Steve,

In the query design view, add the two tables and join them (if not
auto-joined).
Take the fields you want from each table down to the grid.
Make it a Totals query (View > Totals).
Keep the Total function of all fields coming from the staff table to Group
by (default).
Set the Total function of field CompID to Max,.
Set the Total function of all other fields from the compensation table to
First.

This should give you what you want.

HTH,
Nikos

 

[Steve]

Nikos:

Thanks, that works great (I had to use Last rather then
First to get the salary record I wanted). I can use this
to get the current annual salary.

To take this one step further, is there anyway to create
a field in the query grid that will give me the "previous
salary", that is to say the record that is one before
last record on the Compensation table?

That would be really neat!

Steve

-----Original Message-----
Steve,

In the query design view, add the two tables and join them (if not
auto-joined).
Take the fields you want from each table down to the grid.
Make it a Totals query (View > Totals).
Keep the Total function of all fields coming from the staff table to Group
by (default).
Set the Total function of field CompID to Max,.
Set the Total function of all other fields from the compensation table to
First.

This should give you what you want.

HTH,
Nikos

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
raise, amount of raise, current annual salary, etc. A new
compensation record is created whenever there?s a raise for
the employee. Each compensation record has a primary key
field called ?CompID?

I am trying to do a query which lists every employee record
from the staff side and only the most current record (which
will have the highest/max CompID number) of the
compensation table. [And I could therefore create a report
which lists on one line each employee and some fields from
the Staff table as well as the current annual salary, etc.
from the Compensation table]

How can I set up the query grid to accomplish this?

Thanks,

Steve Sherman

.