Need help with simple (?) query.

[JimPDX]

Greetings.

I need help with a simple Access 2000 query. Let’s say I have a table that contains log entries for multiple people with the following fields:

LogID (primary key)
Name
EntryDateTime
TheLogEntry

In this table, there will be multiple entries for multiple people. Let’s assume the values in EntryDateTime will be different for each entry. BUT, do not assume that the EntryDateTime values are sequential when sorted by LogID for any selected Name.

MY GOAL is to create a query that will return one record for each person with fields Name, EntryDateTime and LogID, and the one record for each person will contain values based on the record in the table that has the MOST RECENT EntryDateTime value for each person.

I know in my SQL statement I’ll need a Group By statement for the Name field and to use Max of EntryDateTime, but what do I specify for the LogID field? If I use Group By, then I’ll get more than one record per Name. If I don’t use Group By, how do I tell it to give me the appropriate LogID for the record with the most recent EntryDateTime value?

Thanks in advance!

 

[mgolla]

Use the group by on the LogId and change the select to SELECT DISTINCT

 

[mgolla]

Or use SELECT TOP 1

 

[JimPDX]

Thanks, mgolla, but since LogID is the primary key and every value is unique, when I do what you suggested, I don't get just one entry per Name: I get one entry for each different Name and LogID combination. Here is what I believe you were suggesting:

SELECT distinct Max(tblLog.Entrydate) AS MaxOfEntrydate, tblLog.LogID, tblLog.Name
FROM tblLog
GROUP BY tblLog.LogID, tblLog.Name;

 

[JimPDX]

How exactly would I use SELECT TOP 1 ???

 

[mgolla]

Try this:

SELECT TOP 1 tblLog.LogID,Max(tblLog.Entrydate) AS MaxOfEntrydate, tblLog.Name
FROM tblLog
GROUP BY tblLog.LogID, tblLog.Name;

HTH

 

[JimPDX]

Thanks again, but the last suggestion only returns me ONE record, and not one record for each Name.

 

[mgolla]

Try SELECT DISTINCTROW

 

[JimPDX]

Using DISTINCTROW as in this query

SELECT distinctrow tblLog.LogID,Max(tblLog.Entrydate) AS MaxOfEntrydate, tblLog.Name
FROM tblLog
GROUP BY tblLog.LogID, tblLog.Name;

returns multiple entries per Name.
I need just the one entry per Name.

THanks for your ideas - this is about as far as I got too, in my attempts.

 

[Golom]

How 'bout

SELECT L.LogID, L.Entrydate, L.Name

FROM tblLog L

WHERE L.Entrydate IN

      (Select MAX(Entrydate) From tblLog X
       Where  X.LogID = L.LogID AND
              X.Name  = L.Name )

 

[mgolla]

Try this and see if it doesn't produce what you are looking for:

SELECT distinctrow First(tblLog.LogID)As FirstLogID,Max(tblLog.Entrydate) AS MaxOfEntrydate, tblLog.Name
FROM tblLog
GROUP BY tblLog.LogID, tblLog.Name;

HTH

 

[JimPDX]

Well, the last two suggestions both gave more than one entry per Name. Thanks to the idea of a subquery, I think I've found a solution that will work acceptably:

SELECT tblLog.LogID, tblLog.Name, tblLog.Entrydate
FROM tblLog
WHERE ((([Entrydate] & [Name])
In (SELECT Max(tblLog.Entrydate)&Name
FROM tblLog
GROUP BY tblLog.Name)));

If anyone thinks of a more elegant or simpler solution, please let me know.

thanks again for the help! Much appreciated.

 

[JonFer]

Just an adaptation of Golom's:

SELECT L.LogID, L.Entrydate, L.Name
FROM tblLog L
WHERE L.Entrydate=(Select MAX(Entrydate) From tblLog X
Where X.Name = L.Name )

Since LogID is the primary key, you shouldn't include that in the subquery. The query above finds the greatest entrydate for each NAME. That seems to be what you wanted. But unless EntryDate has the time, too, you could still end up with multiple records for each Name if there are multiple records with the max EntryDate. You indicated that LogID is not indicative of the entry order so it doesn't help.