Selecting max date and then joining with 3rd table

[KimmieB]

I have situation where I have 3 tables in my query. I'm joining table 1 and 2 and selecting the max date in table 2. So far so good. But now I need to join the 3rd table on a column found in table 2.

Ex. table1
id
1
2
3

table2
id date empid
1 1/1/2005 123
1 2/1/2005 123
1 3/1/2005 456

table3
empid empname
123 Daffy
456 Duck

My sql:
SELECT Table1.id, Max(Table2.date) AS MaxOfdate, Table3.empname
FROM (Table1 INNER JOIN Table2 ON Table1.id = Table2.id) INNER JOIN Table3 ON Table2.empid = Table3.empid
GROUP BY Table1.id, Table3.empname;

I keep getting 2 rows back instead of just the one I'm expecing.

id MaxOfdate empname
1 2/1/2005 daffy
1 3/1/3005 duck

 

[lespaul]

Depending on your version of Access, this may work:

SELECT Table1.id, Table2.MaxOfdate, Table3.empname
FROM (Table1 INNER JOIN (SELECT ID, EmpID, Max(Date) AS MaxOfdate FROM Table2 GROUP BY ID, EmpID) As B ON Table1.id = B.id) INNER JOIN Table3 ON B.empid = Table3.empid;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[KimmieB]

Nope, didn't work. I have Access 2002.

 

[PHV]

Create a saved query named, say, qryMaxDate:
SELECT Table1.id, Max(Table2.date) AS MaxOfdate
FROM Table1 INNER JOIN Table2 ON Table1.id = Table2.id
GROUP BY Table1.id;
And now your query:
SELECT M.id, MaxOfdate, empname
FROM (qryMaxDate AS M
INNER JOIN Table2 ON M.id = Table2.id AND MaxOfdate = Table2.date)
INNER JOIN Table3 ON Table2.empid = Table3.empid;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KimmieB]

Thanks, I did use that method and it does work but I just thought I was getting overly elaborate and that there was a simpler way. Guess not. Thanks anyway.

 

[Golom]

Which one are you expecting? Your data contains two records in table3 and you need some way to tell SQL which of the two you want to see from that table. You could use MIN, MAX, FIRST, LAST or TOP 1 to force just one record but you need to state the business problem you're trying to solve to decide which (if any) is the right answer.

 

[PHV]

If you want only one query you may try this:
SELECT M.id, MaxOfdate, empname
FROM ((
SELECT Table1.id, Max(Table2.date) AS MaxOfdate
FROM Table1 INNER JOIN Table2 ON Table1.id = Table2.id
GROUP BY Table1.id
) AS M INNER JOIN Table2 ON M.id = Table2.id AND MaxOfdate = Table2.date)
INNER JOIN Table3 ON Table2.empid = Table3.empid;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KimmieB]

Golum,
I'm trying to get the row from table3 where the name matches the empid from table2 where the date is the maximum which would be "duck".