Need to know number of "X" results in last "Y" records

[CBenfer]

OK all, I think this should be relatively easy, but I'm getting myself confused.

I have a table with:
A dDataID (sequential auto-number)
B dDateTimeEntered (date/time field)
C dFactoryOrder (text)
D dSpecificationID (number)
E dStartWeek (text)
F dIncreasingDecreasing (text, values are limited to "Increasing", "Decreasing", or "NA")

I need a query to look up the last 9 values where C, D, and E match variables from a form, then tell me how many "Increasing" values are contained within that set. I want to store the final value in a combo box on the form. I tried writing the code and this is what I got - design view attached as a pic and SQL below. It looks like the "Top 9" is sorting on the Expr1 field, which isn't what I want, but I'm not sure how to force it to use dDataID or dDateTimeEntered to figure out which records to use.

SQL:
SELECT TOP 9 Sum(IIf([tblData]![dIncreasingDecreasing]="Increasing",1,0)) AS Expr1
FROM tblData
WHERE (((tblData.dFactoryOrder)=[Forms]![frmDataEntryDisplay]![dFactoryOrder]) AND ((tblData.dSpecificationID)=[Forms]![frmDataEntryDisplay]![dSpecificationID]) AND ((tblData.dStartWeek)=[Forms]![frmDataEntryDisplay]![dStartWeek]));

I'm guessing I just need to do something simple, but I could use some help. Thanks!

 

[CBenfer]

For what it's worth, I have a workaround in place for this at the moment - I created a separate query that pulled up the last nine records that met my criteria, then ran my query to get the number of "Increasing" values off of that query instead of directly from the table. Probably not the most efficient method, but it seems to work.

I'd still be happy to hear from anyone on a more appropriate way to get the result I'm looking for.

Thanks!

 

[vbaInet]

What field do you use to determine the last x records? Maybe a date/time stamp or an autonumber sequence?

 

[CBenfer]

What field do you use to determine the last x records? Maybe a date/time stamp or an autonumber sequence?

Either would be fine. I used the autonumber field, but the date/time stamp would work as well.

 

[vbaInet]

I'm asking about existing fields. I wouldn't want you creating another one.

1. Sort by the Autonumber field in DESC order
2. Select the TOP 9
3. Enter the relevant criteria
4. Perform a Count

 

[CBenfer]

I'm asking about existing fields. I wouldn't want you creating another one.

1. Sort by the Autonumber field in DESC order
2. Select the TOP 9
3. Enter the relevant criteria
4. Perform a Count

Either the "A" or "B" field above should work fine.

Is there a way to do that in one query? I managed to do it in two, but I'm guessing there's a better way to get it done.

 

[vbaInet]

I don't know what you mean by "A" or "B" and the steps I gave you is for one query.

 

[CBenfer]

I don't know what you mean by "A" or "B" and the steps I gave you is for one query.

Sorry, A and B referred to my original post - I thought the labels would be less confusing.

On the query - how are you building it? When I tried something similar I got an error. Are you using the query designer, or writing the SQL directly?

 

[vbaInet]

The steps explained is to be done in a query.