[MS_AccessPros] Access query with specific date and earlier

 

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: Parikh Nimish <parikhnd@yahoo.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

---

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

.

__,_._,___