Employee Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Everyone,

Here's what I have:

I have an employee table setup with Social Security as a key, the
Department they are in along with a bunch of other data. I then have another
table setup with an autonumber as a unique key, the social security, hourly
rate and a note field for each employee.

Now, I want to pull a query of all emploee's in a department and their
"current" hourly rate for comparison purposes. That current rate would be
"the most current date for a social security number in that particular table.
"

Now, I figured out how to use the property field of a query, sort descending
by date and then choose to show only 1 record which does give me the correct
information but only for ONE employee at a time.

Does anyone have an idea to accomplish the query that I want?
 
Chris,
Is there a reason you put the Department they are in, in the same table
as the Employee information? It might help to build a department table and
relate it to your Employee table. This might help you get some stuff done
and if I am saying this correctly, it will be in a more normalized form for
a Database.
 
It sounds as if you need to use a coordinated sub-query. That would probably
look something like the query below.

SELECT E.*, R.*
FROM Employee AS E INNER JOIN RateTable as R
WHERE R.ActionDate =
(SELECT Max(Tmp.ActionDate)
FROM RateTable As Tmp
WHERE Tmp.SSN = E.SSN)

Another way to accomplish this would be to use two queries.

First Query would be against the RateTable (your second table) and would return
the Maximum Date for each SSN. You would save that query and then join it in a
second query that would have your Employee Table, RateTable and the saved query.

Join Employee to RateTable on SSN and RateTable to the query on SSN and the ActionDate.
 
Back
Top