Selecting a Particular Record from Groups of Records in a Table

  • Thread starter Thread starter emq_33
  • Start date Start date
E

emq_33

I am trying to develop a SQL query for a report to list an employees
most recent appraisal. The two tables I am working with are
tblEmployee and tblAppraisalHistory. tblEmployee contains all the
things you would expect: name, Employee #, SSA #, address, etc, etc.
tblAppraisalHistory records link to tblEmployee via the Employee #
(employeeID) and also contain appraisal date (AppraisalDate), score,
etc)

So, for a given employeeID in tblAppraisalHistory, I want to get the
most current appraisal record. Then link this to the emplyee name in
tblEmployee using employeeID.

Based on some queries I already had and starting with the most basic
data set, I came up with the following query:

Select e.employeeID, e.LastName, r.LastAppraisal from tblEmployee as e
Left Join (
Select a.employeeID, max(a.AppraisalDate) as LastAppraisal
From tblAppraisal as a) as r
on e.employeeID = a.employeID

Aside from not working, it intuitively does not seem correct as MAX
returns the maximum value for the field.

All that I have read so far gets me tantilizingly close.
Unfortunately, I am not grasping how to select a record from a group of
records based on some criteria where the table has multiple groups of
records to be evaluated.

Can anyone explain this to me in REALLY simple terms? Or is there a
good web site or other reference that covers this?

Thanks!

Don
 
I am trying to develop a SQL query for a report to list an employees
most recent appraisal. The two tables I am working with are
tblEmployee and tblAppraisalHistory. tblEmployee contains all the
things you would expect: name, Employee #, SSA #, address, etc, etc.
tblAppraisalHistory records link to tblEmployee via the Employee #
(employeeID) and also contain appraisal date (AppraisalDate), score,
etc)

So, for a given employeeID in tblAppraisalHistory, I want to get the
most current appraisal record. Then link this to the emplyee name in
tblEmployee using employeeID.

Based on some queries I already had and starting with the most basic
data set, I came up with the following query:

Select e.employeeID, e.LastName, r.LastAppraisal from tblEmployee as e
Left Join (
Select a.employeeID, max(a.AppraisalDate) as LastAppraisal
From tblAppraisal as a) as r
on e.employeeID = a.employeID

Aside from not working, it intuitively does not seem correct as MAX
returns the maximum value for the field.

All that I have read so far gets me tantilizingly close.
Unfortunately, I am not grasping how to select a record from a group of
records based on some criteria where the table has multiple groups of
records to be evaluated.

Can anyone explain this to me in REALLY simple terms? Or is there a
good web site or other reference that covers this?
Click to expand...

It looks like it shud work, except for the final line:

on e.employeeID = r.employeeID

You have to use the alias of the derived table, r, not the alias of the
table, a, in the derived table.
 
Back
Top