Query based on counting text fields

[cool hand]

I have a query that checks whether a item was a pass or fail when it was tested, what i need is to count through the records and see what testing would be required for the next item of that type.
All new items are tested 10 times, after that it can be a 'skipped item' for 9 more, then back on to test as a a '10th lot test'
If a item fails testing, the count has to be reset so that 10 items are tested before it is able to be a 'skipped item'
If an item is a 'skipped item' i need to know how many more items can be skipped before a full test is needed.

I am using a query to give me results by item code, this lists all items with the item code the date and if it passed, skipped, or failed.

I think that an total query should be able to do what i need, or i could just be barking up the wrong tree?!?

the sql for the query is below....

SELECT tbl_max_ship_supp.[MAX Code], Count(tbl_lot_details.[IQA Passed]) AS [CountOfIQA Passed], tbl_lot_details.[IQA Passed], tbl_lot_details.Date
FROM tbl_max_ship_supp INNER JOIN tbl_lot_details ON tbl_max_ship_supp.[MAX Code] = tbl_lot_details.[MAX Code]
WHERE (((tbl_lot_details.[IQA Passed])="pass" Or (tbl_lot_details.[IQA Passed])="fail"))
GROUP BY tbl_max_ship_supp.[MAX Code], tbl_lot_details.[IQA Passed], tbl_lot_details.Date
HAVING (((tbl_max_ship_supp.[MAX Code])=[Enter card part number]) AND ((tbl_lot_details.[IQA Passed])="pass" Or (tbl_lot_details.[IQA Passed])="fail"))
ORDER BY tbl_lot_details.Date DESC;