Query for sequence numbers that are missing?

[lorirobn]

Hi,

I have a table with a sequence number field. Due to a program bug, there are now sequence numbers missing on the table.

The key to my table is RoomID/ItemID/Sequence Number. So I'd need to know sequence numbers out of sequence for a given Room/Item combination.

For example:
Room 200 Item 166 seq #1
Room 200 Item 166 seq #2
Room 200 Item 166 seq #5
(seq #3 was skipped - is there a way to pick that up in a query?).

Any suggestions? I could do it visually, but it'd be a whole lot easier to do it programmatically.

Thanks in advance...
Lori

 

[genomon]

Try looping thru the room IDs/ Item IDs and debug.print
any where seq num <> old seq num + 1?

I have great faith in fools; self-confidence my friends call it.
-Poe

 

[PHV]

You may try this (SQL code):
SELECT A.RoomID, A.ItemID, A.[Sequence Number], Count(*) AS RealSequence
FROM yourTable AS A INNER JOIN yourTable AS B
ON A.RoomID=B.RoomID AND A.ItemID=B.ItemID AND A.[Sequence Number]>=B.[Sequence Number]
GROUP BY A.RoomID, A.ItemID, A.[Sequence Number]
HAVING Count(*) <> A.[Sequence Number]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lorirobn]

Thank you both Genomon and PHV for your responses.

I ended up using PHV's SQL and it worked like a charm. I am always amazed by what can be done via SQL and how much there still is to learn!

Thanks again,
Lori