Query records match >20 display

[cwhite2010]

Hi There

Im hoping someone can help

I have a db which has a query. This query is rather simple where it filters fields based on criteria. Here is the SQL VIEW:

SELECT DSResult.STATUS, DSResult.TESTCODE, DSResult.BATCHNO, DSResult.TESTNO, DSResult.TESTDATE, DSResult.CODE
FROM DSResult
WHERE (((DSResult.STATUS)="TESTED") AND ((DSResult.TESTCODE)="41015" Or (DSResult.TESTCODE)="41016") AND ((DSResult.BATCHNO)<>"1") AND ((DSResult.TESTDATE)>=#1/18/2010#))
ORDER BY DSResult.TESTCODE, DSResult.TESTDATE, DSResult.CODE;


I need to alter this query so that where records that have the same "DSResult.CODE" and counted are greater than 20 occurrences and then display only these records sorted in DSRESULT.Code order.

Can someone please help?

(I'd prefer to write in SQL statement rather than design view as Im trying to practice query writing.)

Appreciate your time

Carla

 

[CJ_London]

try

SELECT DSResult.STATUS, DSResult.TESTCODE, DSResult.BATCHNO, DSResult.TESTNO, DSResult.TESTDATE, DSResult.CODE
FROM DSResult
WHERE (SELECT Count(*) FROM DSResult as tmp WHERE Code=DSResult.code)>20 AND DSResult.STATUS="TESTED" AND (DSResult.TESTCODE="41015" Or DSResult.TESTCODE="41016") AND DSResult.BATCHNO<>"1" AND DSResult.TESTDATE>=#1/18/2010#
ORDER BY DSResult.CODE

 

[cwhite2010]

Thanks ever so much for your help.

Ive replaced the sql xcode with yours and when I run the query it just hangs 'Running Query' but nothing happens.

There are 35000 records

 

[CJ_London]

Can you post the sql you are using - there may be a typo

 

[pr2-eugin]

Nothing fancy, just added some brackets, see if that opens up?

SELECT DSResult.STATUS, DSResult.TESTCODE, DSResult.BATCHNO, DSResult.TESTNO, DSResult.TESTDATE, DSResult.CODE
FROM DSResult
WHERE (((SELECT Count(*) FROM DSResult as tmp WHERE Code = DSResult.code) > 20) AND (DSResult.STATUS = "TESTED") AND ((DSResult.TESTCODE="41015") Or (DSResult.TESTCODE="41016")) AND (DSResult.BATCHNO <> "1") AND (DSResult.TESTDATE>=#1/18/2010#))
ORDER BY DSResult.TESTCODE, DSResult.TESTDATE, DSResult.CODE;

 

[cwhite2010]

Can you post the sql you are using - there may be a typo

Hi Thanks

I simply copied and pasted teh above into the SQL View and hit run:

SELECT DSResult.STATUS, DSResult.TESTCODE, DSResult.BATCHNO, DSResult.TESTNO, DSResult.TESTDATE, DSResult.CODE
FROM DSResult
WHERE (SELECT Count(*) FROM DSResult as tmp WHERE Code=DSResult.code)>20 AND DSResult.STATUS="TESTED" AND (DSResult.TESTCODE="41015" Or DSResult.TESTCODE="41016") AND DSResult.BATCHNO<>"1" AND DSResult.TESTDATE>=#1/18/2010#
ORDER BY DSResult.CODE;

 

[CJ_London]

I can't see anything wrong with that - is the code field indexed? If not it will have an effect on performance.

The other thing to try is used chained queries. Create this query

SELECT Code, Count(*) as CountCode FROM DSResult Group BY code

Remove this code from your 'main' query

(SELECT Count(*) FROM DSResult as tmp WHERE Code=DSResult.code)>20

then drag your new query in and inner join on code and put a criteria against countcode of >20 - all your criteria should be on the same line