UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Generating Report With Specific Date And Earlier, Access 2010    
 
   
NimishParikh
post Dec 27 2017, 06:18 PM
Post#1



Posts: 200
Joined: 30-November 10



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 –

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


CODE
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 –

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


CODE
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

CODE
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

Go to the top of the page
 
RJD
post Dec 28 2017, 11:14 AM
Post#2


UtterAccess VIP
Posts: 7,989
Joined: 25-October 10
From: Gulf South USA


Hi Nimish: I am assuming you made some mistakes in your post. In one of the tables you posted the date Mar/01/2017, but then showed a date as Mar/01/2016 in the results. Also, there are six resulting records in the query example, whereas I would expect seven.

Take a look at the db attached and see if this is what you are trying to do. Or please clarify the requirements for us.

Please note also that I used Format to drive the date comparison field and criteria, since i assume you are using European date formats.

HTH
Joe
Attached File(s)
Attached File  SpecificDateAndEarlier.zip ( 20.04K )Number of downloads: 3
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
RJD
post Dec 28 2017, 11:35 AM
Post#3


UtterAccess VIP
Posts: 7,989
Joined: 25-October 10
From: Gulf South USA


Oh, just a further note: Since both of your tables contain only the A project, I did not join the tables in the query. If there are multiple projects, you should LEFT JOIN on the ProjectCode.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd January 2018 - 07:04 PM