Advanced SQL Query

[Wes Henry]

I have a table and I am trying to write an SQL query for it. I am trying
to get the previous X-number of records out of it but can't figure out
the best way to go about it, so any help would be great. The table
looks something like this:

ID Week Year Home_Name Away_Team Home_Rating Away_Rating
5 1 2005 New York Arizona 14 -14
1 3 2005 Seattle Arizona 15 -15
41 5 2005 Arizona Carolina 35 -35
3 10 2005 Detroit Arizona 8 -8
2 11 2004 St. Louis Arizona 26 -26
143 12 2004 Arizona Jacksonville 6 -6
6 13 2004 San Francisco Arizona 18 -18
7 15 2004 Houston Arizona -5 5
235 16 2004 Arizona Philadelphia 11 -11
4 17 2004 Indianapolis Arizona 93 -93

My, SQL query coding looks like this so far:

SELECT Subselect.Team, Avg(Subselect.TeamRating) AS AverageRating
FROM
(
SELECT [Home_Name] AS Team, [Home_Rating] AS TeamRating
FROM tbl_main
UNION SELECT [Away_Name] AS Team, [Away_Rating] AS TeamRating
FROM tbl_main
) AS Subselect
GROUP BY Subselect.Team;

So, for all intensive purposes, let's say that I want the last 6 Arizona
(home and away) games to show up in the query (week 17 of 2004 - Week 10
of 2005). I can't use the Week number because a <=6 would just show me
weeks 1, 3 and 5, which is only the last 3 weeks. I can't do a <=6 on
the ID field because that would give me records 1-6, which aren't the
last 6 games. It could be that this is right in front of my face, or
even not, but I just can't get it. Any help would be greaty appreciated.

-Wes