Pull Last Three Results Every Quarter

[Guest]

I have to table thats contains survey data for a year, every quarter of the
year we recieve new data. I have it set up so that the data runs through
queries to get some averages and totals. I am trying to set up a query, to
take the last three quarters of data after every new quarter. For Ex. I have
the numbers 1, 2, 3, 4, 5 ,6. I want the query to show the data for quarters
4, 5, 6 but when I insert quarter 7 data the query should pull up 5, 6, 7.
Here's the SQL Code I have to try and pull the last three numbers, but it
stills just gives me all the quarters

SELECT a.[Quarter]
FROM tblQuarterlySurveyData a
WHERE EXISTS
( SELECT Max(b.[Quarter]), Max(b.[Quarter]), Max(b.[Quarter])
FROM tblQuarterlySurveyData b
WHERE b.[Quarter] = a.[Quarter]);

Any help would be great. Thanks

 

[Guest]

Top 3 will return the top 3 records, so you can use Top 3 * and order by the
record number DESC i.e.

SELECT TOP 3 *
FROM TableName
ORDER BY RecordId DESC

This assumes your RecordID is the number you specified, i.e. 1, 2, 3, 4,
5... Ordering DESC will order the records from largest value to smallest.
You could also order by a create_date DESC, if you have a column that tracks
when each record was created.

 

[Guest]

The criteria you specify in your where clause won't limit anything, since
you're selecting against the same table. It will always select everything,
since every b.quarter MUST have a matching a.quarter- a and b refer to the
same table. And what is gained by specifying 'Max(b.[Quarter]' three times in
the nested select?

I suspect that what you are trying to get is:
select a.Quarter from tblQuarterlySurveyData a
where a.quarter >= (select max(quarter) from tblQuarterlySurveyData) - 3;

Good Luck!

 

[Guest]

Use two queries --
LatestQuarter
SELECT Max(tblQuarterlySurveyData.Quarter) AS MaxOfQuarter
FROM tblQuarterlySurveyData;

Last3QtrsData
SELECT tblQuarterlySurveyData.Quarter, tblQuarterlySurveyData.Data
FROM tblQuarterlySurveyData, LatestQuarter
WHERE (((tblQuarterlySurveyData.Quarter) Between [MaxOfQuarter]-2 And
[MaxOfQuarter]));

 

[Dale Fye]

Assuming you have multiple entries for each quarter, I think what you are
looking for is:

Select a.* From tblQuarterlySurveyData a
WHERE a.Quarter IN (SELECT TOP 3 tblQuarterlySurveyData.Quarter
FROM tblQuarterlySurveyData
GROUP BY tblQuarterlySurveyData.Quarter
ORDER BY tblQuarterlySurveyData.Quarter
DESC)

HTH
Dale