ms access complicated query. querying last two entries by date for each ID. very slow

Question

I have two tables. Widgets, which has information about each widget (Color, size, etc); each widget has a unique ID, WidgetID.
The other table is Tests, and it contains information about multiple tests that were run on each widget. This table, therefore, has multiple rows for each WidgetID. It contains information that we can call (WidgetID, Date, Param1, Param2, etc); test information.
I have written a query that finds, for each WidgetID, the most recent two tests by date. The SQL looks like this:

SELECT Widgets.WidgetID, Widgets.Color, Widgets.Size, T.Date, T.Param1, T.Param2,*
FROM Tests AS T INNER JOIN Widgets ON T.WidgetID=Widgets.WidgetID
WHERE (((Select COUNT(*) FROM Tests
WHERE WidgetID = T.WidgetID AND Date > T.Date)) < 2 );

This works very well. However, it gives me too many widgets. I have created a query that filters the widgets called WidgetFilter. It basically just chooses the ones i want based on whatever i choose. The idea was that I would run the same query replacing "Widgets" in the code above with "WidgetFilter." However, when I do this it takes forever. In fact, it just freezes. I left it for an hour and a half and it just sat there and i had to alt ctl delete. My only thought is that it is querying the WidgetFilter query for every row of Tests (and thats a lot of rows). I also tried applying the filter criteria within the original query. I get the same result.

Is there a better way to do this? Either a single query that does it all and possibly doesnt even look like this or what i was thinking is that there should be a way to run the WidgetFilter query once and make that data look to access like a table (isnt there such a thing as a temp table). That way it doesnt run WidgetFilter for every item in Tests.

EDIT:
WidgetFilter is actually pretty complex. I have created these GUI pick tables where the user sees two columns. The choices on the left and the list he/she is creating on the right and in the middle there is an add button and a delete button. They then call the report which executes WidgetFilter, yadda yadda yadda. Anyway, when the user adds an item, it adds that item to a table. So for the Widget category, Color, there will be a table called ColorList. The user constructs this list through the gui. There are three of these guis (Color, Size, Type). For each of them there is a table and there is then a Global boolean (eg, ColorFlag) that tells which filter to use (Color, Size, or Type).
So, in the WidgetFilter query, the criteria box under Color will have this:
In(Select Color From ColorList)
and there is an expression column Expr1: getColorFlag() which is a module that returns the value of the Global variable ColorFlag. and it is true at the same time the color table is applied. So, when all is said and done there are three rows of criteria. The code looks like this:

Select Widgets.WidgetID, Widgets.Color, Widgets.Size, Widgets.Type
From Widgets
WHERE (getColorFlag() = True AND (Widgets.Color) In(Select Color FROM ColorList))
OR (getSizeFlag() = True AND (Widgets.Size) In(Select Size FROM SizeList))
OR (getTypeFlag() = True AND (Widgets.Type) In(Select Type FROM TypeList))

Answer 1

One thing you may not be aware of. I believe that most versions of Access (don't know about 2007) will go off to loopy land if you have a sub-select that returns a NULL value for any row. So, if there is a NULL Color, Size, or Type in any of those tables that would cause the symptoms you're seeing.

Answer 2

Ok. Here's what I ended up doing. I created a temp table through VBA code like this:

Function createWidgetFilterTemp()
Dim mySQL
Dim deleteSQL
deleteSQL = "DELETE * FROM TempWidgetFilter"
mySQL = "SELECT * INTO TempWidgetFilter FROM WidgetFilter" DoCmd.SetWarnings False
DoCmd.RunSQL deleteSQL
DoCmd.RunSQL mySQL
DoCmd.SetWarnings True
End Function

I Inner Joined TempWidgetFilter and Tests in a query called WidgetCombo. This gave me access to all the Test information and all the Widgets information for only the WidgetIDs that i was interested in. I tried to do the same query with the Select Count(*) statement, but i got the same problem with Access freezing up. So, I then created another temp table through VBA using code like this:

Function createWidgetTemp()
Dim mySQL
Dim deleteSQL
deleteSQL = "DELETE * FROM TempWidgetCombo"
mySQL = "SELECT * INTO TempWidgetCombo FROM WidgetCombo" DoCmd.SetWarnings False
DoCmd.RunSQL deleteSQL
DoCmd.RunSQL mySQL
DoCmd.SetWarnings True
End Function

I ran both of these functions whenever I called for the querys from the form. This worked!! It worked fairly quickly, actually. I think it would have worked without the first temp table, but having that as a temp table rather than just a query made another part of my app run faster so i left it. By the way, I had to run it once with the delete line commented off so that it would create the table. I think i might have been able to just create the table by hand, but this way it got all the fields in there in the correct way. Thanks for the help, I hope this helps someone else who comes by in the future looking for temp table help.