Get 3 lines for Each SN 1

[uncleG]

Hi All

The following query returns 11 test lines (records) for 2 SerialNumbers.
6 lines for SerialNumber 1 and 5 lines for SerialNumber 2
My documents will require only 3 lines for each individual SerialNumber

First: I would like it to limit the query to return only 3 records for each SerialNumber.

All other required validations have occured in the previous query qryDsTilt and data_ID (PK AN) will return the Last 3 trials performed as earlier trials may have been performed while the product was being adjusted.

Second: If there is only 1 or 2 records for any given Serial Number I would like to be able to warn the user and
then give him/her the option to continue or cancel. Seems there are exceptions to every rule.

Query:
SELECT (Val([SerialNumber])) AS SerialNum, qryDSTilt.rtn_val_2, qryDSTilt.rtn_val_3, qryDSTilt.rtn_val_0, qryDSTilt.rtn_val_1, qryDSTilt.[Pass/Fail], qryDSTilt.ResetPass, qryDSTilt.data_ID
FROM qryDSTilt
WHERE (((qryDSTilt.[Pass/Fail])="Pass") AND ((qryDSTilt.ResetPass)="Pass"))
ORDER BY (Val([SerialNumber])), qryDSTilt.data_ID DESC;

Thanks in Advance,
UncleG

 

[PHV]

One way:

SELECT Val(A.SerialNumber) AS SerialNum, A.rtn_val_2, A.rtn_val_3, A.rtn_val_0, A.rtn_val_1, A.[Pass/Fail], A.ResetPass, A.data_ID
FROM qryDSTilt AS A
WHERE A.[Pass/Fail]='Pass' AND A.ResetPass='Pass' AND A.data_ID In (
  SELECT TOP 3 data_ID FROM qryDSTilt WHERE SerialNumber=A.SerialNumber
  AND [Pass/Fail]='Pass' AND ResetPass='Pass' ORDER BY 1 DESC)
ORDER BY Val(A.SerialNumber), A.data_ID DESC

Another way:

SELECT Val(A.SerialNumber) AS SerialNum, A.rtn_val_2, A.rtn_val_3, A.rtn_val_0, A.rtn_val_1, A.[Pass/Fail], A.ResetPass, A.data_ID
FROM qryDSTilt AS A INNER JOIN qryDSTilt AS B ON A.SerialNumber=B.SerialNumber AND A.data_ID<=B.data_ID
WHERE A.[Pass/Fail]='Pass' AND A.ResetPass='Pass'
AND B.[Pass/Fail]='Pass' AND B.ResetPass='Pass' 
GROUP BY Val(A.SerialNumber), A.rtn_val_2, A.rtn_val_3, A.rtn_val_0, A.rtn_val_1, A.[Pass/Fail], A.ResetPass, A.data_ID
HAVING Count(*)<=3
ORDER BY Val(A.SerialNumber), A.data_ID DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[uncleG]

Thanks PHV,
I Like them both, now to decide which to use.
I have named a command button in my application in your honor.
Thanks again,
UncleG