the MAX function in queries

[welshone]

Hello All,

I hope somebody can help....

I am trying to run a report just to show the newest date entered for a visitor.

I have a visitor table ( name, address, photo ) and a visitorlink table ( date visited ).

how do I use the MAX function on date_reissued to say :

SELECT dbo_DATAforPHOTOID.Forename, dbo_DATAforPHOTOID.Surname, dbo_DATAforPHOTOID.ADID, tblPhotos.Photo, tblReissued.Date_Reissued, dbo_DATAforPHOTOID.Post, dbo_DATAforPHOTOID.CompanyName
FROM (dbo_DATAforPHOTOID LEFT JOIN tblPhotos ON dbo_DATAforPHOTOID.ADID = tblPhotos.ADID) LEFT JOIN tblReissued ON dbo_DATAforPHOTOID.ADID = tblReissued.ADID
WHERE (((dbo_DATAforPHOTOID.ADID)=[forms]![personnel_details]![adid]));

??
thanks in advance

 

[jebry]

Hi!

The syntax is simple, Max(tblReissued.Date_Reissued) As LatestDate(or whatever you want to call it). You will also need to add a Group By clause which, I think, comes after the Where clause. You will need to group by each of the other fields.

hth
Jeff Bridgham

 

[tlbroadbent]

You may want to use a subquery to determine the last reissued date per ADID as in the following query.

SELECT
d.Forename, d.Surname,
d.ADID, p.Photo,
r.Last_Reissued,
d.Post, d.CompanyName

FROM (dbo_DATAforPHOTOID As d
LEFT JOIN tblPhotos As p
ON d.ADID = p.ADID)
LEFT JOIN
(Select ADID, Max(Date_Reissued) As Last_Reissued
FROM tblReissued Group By ADID) As r
ON d.ADID = r.ADID

WHERE d.ADID)=[forms]![personnel_details]![adid]; Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.