RE: [MS_AccessPros] Getting records with highest date which less than the specific date

 

Hi Nimish,

 

You can check with this sql statement:

 

SELECT tblPrjMTORevision.ProjectCode, tblPrjMTORevision.MTORevision, tblPrjMTORevision.MTORevisionDate, tblPrjPipeClassMaster.PipeClass, tblPrjPipeClassMaster.PipeClassRevision, tblPrjPipeClassMaster.PipeClassRevisionDate

FROM tblPrjMTORevision INNER JOIN tblPrjPipeClassMaster ON tblPrjMTORevision.ProjectCode = tblPrjPipeClassMaster.ProjectCode

WHERE tblPrjPipeClassMaster.PipeClassRevisionDate <= tblPrjMTORevision.MTORevisionDate;

 

Bye

Davide

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, December 28, 2017 12:12 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Getting records with highest date which less than the specific date

 

 

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 may revise PipeClass as well. What I need to is a query that generates a report showing every PipeClass and 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

 

PS: I've posted this question to other discussion group as well.

 

__._,_.___

---

Posted by: Davide Franceschini <bigcat_21@hotmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___