I have a ticket table, it's used as history record. Each item# has only one line, qty can be up and down.
But if no problem been found on that day it become 0. If same item# keep show up on the list every day, it means a more serious problem and I have to identify how many days it stay there.
table structure as
item# qty ID date
A001 4 qc12 20070510
A003 3 qc03 20070510
A001 5 qc12 20070511
A004 2 qc11 20070511
A001 happened two days in a row, and will be pick up to show on a report.
I need to create a query to find out for how many days same item# been stay in the list continuously. ie
create a report to show how many items are on the list more than 2 days in a row and how many days it stay on the list. If it has become 0 then that item will will not show on the list and restart the counter again. Hope I make it clear enough. Thanks for help.