Need To Find Last Date W/Info

[pcm1977]

I have a table that acts as a log for data contained in a separate
table. I need to retrieve the last entered date, location, and status
for each different record in the main table from the log table.

Fields in the log table (aka tblLog):
Key (autonumber)
NMR (number)
Date (general date/time)
CurrentLocation (text)
Status (text)

The NMR is the common field between the tables.

Thank You

 

[pcm1977]

Try one of these:

SELECT L.Key, L.NMR, L.Date, L.CurrentLocation, L.Status
FROM tblLog As L
        INNER JOIN tbldata As T
                ON T.NMR = L.NMR
WHERE L.Date = (SELECT Max(X.Date)
                                                                        FROMtblLog As X
                                                                        WHERE X.NMR = L.NMR)
or

SELECT L.Key, L.NMR, L.Date, L.CurrentLocation, L.Status
FROM tblLog As L
        INNER JOIN (SELECT NMR, Max(X.Date)
                                                          FROM tblLog As X INNER JOIN tbldata As T
                                                                        ON X..NMR = T.NMR
                                                          GROUP BY X.NMR) As M
                ON L.NMR = M.NMR And L.Date = M.Date

I'm pretty sure the second one will be faster.

Thank You
However i get an error message when i try to run the query.

"The Microsoft Jet database engine cannot find the input table or
query 'tbldata'. Make sure it exists and the name is spelled
correctly."

Was i supposed to replace the reference to tbldata with a different
table/query name?
Should a create a new table named tbldata?

Thank you

 

[pcm1977]

However i get an error message when i try to run the query.

"The Microsoft Jet database engine cannot find the input table or
query 'tbldata'.  Make sure it exists and the name is spelled
correctly."

Was i supposed to replace the reference to tbldata with a different
table/query name?

Your question mentioned a "main table" so I made up its name
(tbldata).  

It's also possible (likely?) that I misunderstood which
table contains the date, location, and status fields.  If
they are not in tblLog, then the first query would need to
use T instead of L in the Select clause and the second query
would need to be rewritten.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Thank You very much this worked perfectly.

Now all i need to do is understand exactly how it all works. I have
not spent a whole lot of time on SQL statements (OK this is my
first). Do you know/recommend any web sites or books that could help
me understand this stuff better?

Thank You Again !!!

 

[pcm1977]

I'm not a good one to recommend those things since it has
been many, many years since I learns about this stuff.

However, I think
        "SQL Queries for Mere Mortals"
by John Viescas and Michael Hernandez
would be a good bet.

Check Amazon for Access SQL to locate other books that I
have never heard of before.  Just be careful to get
something that uses Access.  While there are many
commonalities, other SQL dialects may leave you somewhat
confused when using Access' version of SQL.

Thank you!!