Help with most recent 2 weeks query.

[tplee]

Hello,

I'm trying to run a query that returns data for the most recent 2 weeks.

For example the table looks something like this:

Attachment 5364

The table is constantly updated so I want the query to return the most recent 2 weeks of data, otherwise i would just type in the specific criteria. So for this example it would return the data for WE ending 12/03/2011 and 11/25/2011. And so if the table was updated with the most recent data for WE ending 12/15/2011 it would return weeks 12/15/2011 and 12/03/2011. Keep in mind im not good with sql. I do everything in design mode.

Thanks in advance

[orange]

You will need a query along these lines:substitute your actual table name for yourtable

Select student
,[test score]
,state
,[week ending date] from YourTable 
where [week ending date] in
     (SELECT TOP 2 [week ending date]
       From YourTable
ORDER BY [week ending date] DESC)

You could add an Order By [Test Score] DESC; if you wanted the results in Highest score to lowest score order for all scores during the last two week period.
You should avoid special characters and spaces in your field and object names.

[alansidman]

Orange:
I did something similar but with the TOP 2, if it there is more than one record for that week, it does not bring back two weeks but the two records from that week. Not sure how to remedy this for the OP. I changed TOP 2 to TOP 3 to get both weeks. But if there are only one per week, this will return three records.

Alan

[orange]

Alan,

I just get the weekending date in the sub query, so all records with either of the 2 week ending dates should be selected.

If there were 100 records with either of these week ending dates, all should be selected. Do you have a test set up-- I don't.

[alansidman]

I set up a test but now it is only giving me the November Date , but not the December Date. Very mysterious. I'll post it so you can see.

[orange]

I have acc 2003 not 2007 or 2010. Can't use an accdb.

[alansidman]

I realized after I posted. Reposted as 2003 file. My bad.

[orange]

Thanks Alan.

I think this solves it -- needs the Group by (to get the 2 ) and DESC (to get the latest )

SELECT Sheet1.student, Sheet1.testscore, Sheet1.state, Sheet1.WEdate
FROM Sheet1
WHERE Sheet1.WEdate In (SELECT TOP 2 [WEdate]
From Sheet1
Group by WEDate
ORDER BY Sheet1.WEdate DESC);