SQL help

[Eddy]

I have a table with who is holding an invoice file. The table holds the
invoice id, name, date the file was assigned to them. What I am trying to do
is find who is assigned to the invoice.

I run this statement and it pulls me the "right" date

SELECT CollectorDetails.InvoiceID, Max(CollectorDetails.Date) AS MaxOfDate
FROM CollectorDetails
GROUP BY CollectorDetails.InvoiceID;

Problem is that I want the NAME of the collector and if I add it, no matter
what I do it shows everyone that was assigned to ever invoice. All I want is
the invoice id, the date and the name of the collector holding it.

Can anyone help??

Thank you.

 

[John Spencer]

Save the query
Make a new query with the saved query and the table CollecterDetails.
Join the query to the table on InvoiceID and Date
Add any fields you want to see.

One single query solution.
SELECT CollectorDetails.InvoiceID, CollectorDetails.[Date], Collector.[Name]
FROM CollectorDetails
WHERE CollectorDetails.[Date] IN
(SELECT Max(Tmp.Date)
FROM CollectorDetails as Tmp
WHERE tmp.InvoiceID = CollectorDetails.InvoiceID)

Another possible solution - this may or may not work depending on the flavor
of Access you are using
SELECT C.InvoiceID, C.[Date], C.[Name]
FROM CollectorDetails as C INNER JOIN
(SELECT CollectorDetails.InvoiceID, Max(CollectorDetails.Date) AS MaxOfDate
FROM CollectorDetails
GROUP BY CollectorDetails.InvoiceID) As T
ON C.InvoiceID = T.InvoiceID AND C.[Date] = T.MaxOfDate