Query Help - Return latest entry for distinct rows

[mdex]

I have a table that includes an ID (key), TeamName, AccountType and DateChecked fields, amongst a few others.



What I would like to be able to do is return the latest entry for each distinct TeamName/AccountType combination. I've tried to follow the example here as shown below but get an error that "Specified field 'TeamName' could refer to more than one table listed in the FROM clause of your SQL statement."

Select TeamName, AccountType, ID
from Checks a inner join
(select TeamName, MAX(ID) as maxID
from checks group by TeamName) b
on a.TeamName=b.TeamName and a.ID=b.ID

I've tried switching ID and DateChecked within the query tna. I'm already way past my knowledge of Access/SQL and as always am trying to bastardise code where I can. Any help is gratefully received.

[ranman256]

Make Q1 to get MAX(Date),team,acct.
make Q2 using Q1 and data table to get all the fields you wish to see.

[mdex]

Thanks for ths pointer.

I'm struggling to get my head around the logic of this so please bear with me.

I have Q1 to get the latest entry for each combination of TeamName/AccountType

SELECT Checks.TeamName, Checks.AccountType, MAX(DateChecked) as max_date
FROM Checks
GROUP BY Checks.TeamName, Checks.AccountType;
What I can't figure out is how to create Q2 to show the rest of the fields to accompany those found in Q1. I've tried reading up on joins but I've not managed to hit on a syntax that will run, nevermind give me the outcome I desire.

[Homegrownandy]

In query design view use Q1 as if it were a table. have that on there with the other table you wish to link to.

drag the ID so it has a relationship then double click the fields you want.

Thats if im not misunderstanding you.

[mdex]

In query design view use Q1 as if it were a table. have that on there with the other table you wish to link to.

drag the ID so it has a relationship then double click the fields you want.

Thats if im not misunderstanding you.

That's exactly it. Simple now you mention it.

Thanks