Append Query with Date

[Bryan Hughes]

Hello,

I have an append query that gets info from two tables with the inner table
getting the last date (max(MyDate)) as part of the criteria.

INSERT INTO tblUSys_GT_Case_File_Monthly_Activity_Status
GTFID, CAD, CAS, CFID, CM, EED, IOD, CDID)
SELECT tblGT_Case_File.GTFID,
CFA.LstCAD AS CAD, CFA.CAS, tblGT_Case_File.CFID, tblGT_Case_File.CM,
tblGT_Case_File.EED, tblGT_Case_File.IOD, tblGT_Case_File.CDID
FROM tblGT_Case_File
INNER JOIN (SELECT GTFID, MAX([CAD]) AS LstCAD, CAS
FROM tblGT_Case_File_Monthly_Activity_Status
WHERE CAS = True GROUP BY GTFID, CAS) AS CFA
ON tblGT_Case_File.GTFID = CFA.GTFID WHERE CFA.LstCAD <=
tblGT_Case_File.EED;

How can I also add criteria to the query that will only find records, that
match month and year for the max date (LstCAD), from a date entered?

-TFTH
Bryan

 

[[MVP] S.Clark]

I typically do this with two queries. The first query finds the max date
per entity. The second query uses the first query linked to the raw data,
to find all the info.