How to return latest result 2

[TheFitz]

Hi all,

I've got the following query (It's probably easier to copy to Access to view) which is attemping to take the names from the "People" table and whether they have booked in or out etc on the Register table.

SELECT tblPeople.PEO_ID, [PEO_FORENAME] & " " & Trim([PEO_SURNAME] & " " & [PEO_SUFFIX]) AS [Full Name], tblRegister.REG_STATUS AS Status, tblRegister.REG_OTHER_INFO AS [Other Info], Last(tblRegister.REG_LAST_MOD) AS LastOfREG_LAST_MOD
FROM tblPeople INNER JOIN tblRegister ON tblPeople.PEO_ID = tblRegister.REG_PEO_ID
WHERE (((tblRegister.REG_DATE)=Format([Forms]![frmBookInOut]![txtDate],"DD-MMM-YYYY")))
GROUP BY tblPeople.PEO_ID, [PEO_FORENAME] & " " & Trim([PEO_SURNAME] & " " & [PEO_SUFFIX]), tblRegister.REG_STATUS, tblRegister.REG_OTHER_INFO, tblPeople.PEO_SURNAME, tblPeople.PEO_FORENAME
ORDER BY tblPeople.PEO_SURNAME, tblPeople.PEO_FORENAME;

The bit I'm confused with, is how do I only pull the latest record from the Register table?? This is an issue I've been working on for sometime in various guises, but I can't seem to work out the last little bit. Max or Last functions on the field don't seem to be working.

Thanks for your time,

Regards,

Fitz

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!

 

[lespaul]

maybe (typed, not tested):

SELECT P.PEO_ID, [PEO_FORENAME] & " " & Trim([PEO_SURNAME] & " " & [PEO_SUFFIX]) AS [Full Name], R.REG_STATUS AS Status, R.REG_OTHER_INFO, LastOfREG_LAST_MOD
FROM tblPeople P
INNER JOIN (SELECT REG_PEO_ID, REG_OTHER_INFO, REG_STATUS, MAX(REG_LAST_MOD) As LastOfREG_LAST_MOD FROM tblRegister GROUP BY REG_PEO_ID, REG_OTHER_INFO, REG_STATUS) R ON P.PEO_ID = R.REG_PEO_ID

Leslie

Have you met Hardy Heron?

 

[PHV]

What about something like this ?

SELECT P.PEO_ID, [PEO_FORENAME] & " " & Trim([PEO_SURNAME] & " " & [PEO_SUFFIX]) AS [Full Name], R.REG_STATUS AS Status, R.REG_OTHER_INFO AS [Other Info], R.REG_LAST_MOD AS LastOfREG_LAST_MOD
FROM (tblPeople AS P
INNER JOIN tblRegister AS R ON P.PEO_ID = R.REG_PEO_ID)
INNER JOIN (SELECT REG_PEO_ID, Max(REG_LAST_MOD) AS LastDate FROM tblRegister
  WHERE REG_DATE=Format([Forms]![frmBookInOut]![txtDate],"DD-MMM-YYYY") GROUP BY REG_PEO_ID
) AS L ON R.REG_PEO_ID = L.REG_PEO_ID AND R.REG_LAST_MOD = L.LastDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheFitz]

Thanks guys, I've looked at both of them and they've pointed me in the right direction.

Many thanks for your time.

TheFitz

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!