Filtering Query to get final given period

[accessNator]

I have a sample query from a table and this is my result. My goal is to get the latest and greatest record of a given Start_Period and End_Period.
In the Revision Column, if it has a -1 value it supercedes the given period with a Revision of 0. If the revision of a given period already has a -1 then it supercedes it if the Report_Month is the latest. And if the same is true in the proceding example, then it looks at the Submission_Date.

I have a delimma with the Start_Period 5/1/2008 and End_Period of 05/31/2008

WorksheetId...Submission_Date...Report_Month...Start_Period...End_Period...Revision...Amount...Original_WorkSheet_ID
10..............................4/8/2008.......3/1/2008.......3/31/2008....0..........100...........................
15..............................5/8/2008.......4/1/2008.......4/30/2008....0..........150...........................
20..............................6/8/2008.......5/1/2008.......5/31/2008....0..........100...........................
25..............................7/8/2008.......6/1/2008.......6/30/2008....0..........175...........................
30..............................8/8/2008.......7/1/2008.......7/31/2008....0..........110...........................
35..............................9/8/2008.......8/1/2008.......8/31/2008....0..........140...........................
40..............................10/8/2008......9/1/2008.......9/30/2008....0..........150...........................
50..............................11/8/2008......10/1/2008......10/31/2008...0..........200...........................
60..............................12/8/2008......11/1/2008......11/30/2008...0..........150...........................
65..............................01/9/2009......05/1/2008......05/31/2008...-1.........300........20.................
70..............................02/9/2009......05/1/2008......05/31/2008...-1.........500........65.................
80.............02/10/09.........02/9/2009......05/1/2008......05/31/2008...-1.........175........70.................
90.............03/1/09..........03/9/2009......05/1/2008......05/31/2008...-1.........200........80.................
95.............03/2/09..........03/9/2009......12/1/2008......12/31/2008...0..........400...........................

My goal is for my final recordset to look like this.

WorksheetId...Submission_Date...Report_Month...Start_Period...End_Period...Revision...Amount...Original_WorkSheet_ID
10..............................4/8/2008.......3/1/2008.......3/31/2008....0..........100...........................
15..............................5/8/2008.......4/1/2008.......4/30/2008....0..........150...........................
90.............03/1/09..........03/9/2009......05/1/2008......05/31/2008...-1.........200........80.................
25..............................7/8/2008.......6/1/2008.......6/30/2008....0..........175...........................
30..............................8/8/2008.......7/1/2008.......7/31/2008....0..........110...........................
35..............................9/8/2008.......8/1/2008.......8/31/2008....0..........140...........................
40..............................10/8/2008......9/1/2008.......9/30/2008....0..........150...........................
50..............................11/8/2008......10/1/2008......10/31/2008...0..........200...........................
60..............................12/8/2008......11/1/2008......11/30/2008...0..........150...........................
95.............03/2/09..........03/9/2009......12/1/2008......12/31/2008...0..........400...........................

I would appreciate any assistance on this.

 

[khawar]

Can you upload some sample data to work on

 

[accessNator]

Can you upload some sample data to work on

Attached is the sample.mdb

The data can be found in SampleHistory Table

What I would like to have is the end result that looks like the records in the FinalSampleHistory table. The data is the same as SampleHistory table but I maually deleted the records that I dont need. I am looking to filter to get the most recent records for a given start and end period. Please look at my original question for the explanation.

Thanks for your assistance. I appreciate it.

 

[khawar]

Download the attached file
Result you want is in the query "FinalResult"

 

[accessNator]

Download the attached file
Result you want is in the query "FinalResult"

khawar,
Thank you very much. That is exactly what I needed. I modified it to meet my actual needs. This definitely put me in the direction to finish my project. Thanks again for your assistance.

Cheers!