Query to find latest dates from another table

[Nimish]

Hi,

Merry Christmas and Happy new year to all.

It is difficult to describe my problem, but, I will try to my best to delineate it.

I have following two tables –

tblPrjMTORevision
=============
ProjectCode Text Primary Key
MTORevision Text Primary Key
MTORevisionDate Date
MTORevisionDescription Text




tblPrjPipeClassMaster
================
ProjectCode Text Primary Key
PipeClass Text Primary Key
PipeClassRevision Text Primary Key
PipeClassRevisionDate Date

We revise our material take off (MTO) quarterly and it is tracked through tblPrjMTORevision and its child table. Between two revisions of MTO we may revise PipeClass as well. What I need to is a query that generates a report showing every PipeClass and highest PipeClassRevision having PipeClassRevisionDate less than or equal to MTORevisionDate against each revision of MTO.

As an example, if the tables have following values –

tblPrjMTORevision
=============
ProjectCode MTORevision MTORevisionDate
A A Dec/01/2016
A B Mar/01/2017

tblPrjPipeClassMaster
================
ProjectCode PipeClassRevision PipeClass PipeClassRevisionDate
A A AAA Nov/01/2016
A B AAA Dec/27/2016
A A ABA Nov/19/2016
A A BAA Nov/21/2016



Based on these two tables, I need a query that produces the following result

ProjectCode MTORevision MTORevisionDate PipeClass PipeClassRevision PipeClassRevisionDate
A A Dec/01/2016 AAA A Nov/01/2016
A A Dec/01/2016 ABA A Nov/19/2016
A A Dec/01/2016 BAA A Nov/21/2016
A B Mar/01/2016 AAA B Dec/27/2016
A B Mar/01/2016 ABA A Nov/19/2016
A B Mar/01/2016 BAA A Nov/21/2016

Any help will be greatly appreciated.

Nimish

[ArviLaanemets]

At start, create a saved query (I'll give SQL syntax for queries) p.e. qryLastPipeClassRevisions:

SELECT DISTINCT 
mto.ProjectCode, mto.MTORevision, mto.MTORevisionDate, pipe.PipeClass, 
(SELECT MAX(pipe0.PipeClassRevisionDate) FROM tblPrjPipeClassMaster AS pipe0 
WHERE  pipe0.ProjectCode = mto.ProjectCode AND pipe0.PipeClass = pipe.PipeClass 
AND pipe0.PipeClassRevisionDate <= mto.MTORevisionDate GROUP BY pipe0.ProjectCode, pipe0.PipeClass) AS LastPipeClassRevisionDate
FROM tblPrjMTORevision AS mto INNER JOIN tblPrjPipeClassMaster AS pipe ON mto.ProjectCode = pipe.ProjectCode;

Then use this query as base for your report SQL:

SELECT qlast.ProjectCode, qlast.MTORevision, qlast.MTORevisionDate, 
qlast.PipeClass, pipe.PipeClassRevision, qlast.LastPipeClassRevisionDate
FROM qryLastPipeClassRevisions  AS qlast INNER JOIN tblPrjPipeClassMaster AS pipe
ON (qlast.LastPipeClassRevisionDate = pipe.PipeClassRevisionDate) 
AND (qlast.PipeClass = pipe.PipeClass) 
AND (qlast.ProjectCode = pipe.ProjectCode);