SQL Microsoft Access

I have a table of transactions in Microsoft Access that contains many transactions for many开发者_StackOverflow社区 vendors. I need to identify if there is sequential transaction numbering for each vendor. I don't know what the sequence will be or the number of transactions per vendor. I need to write a SQL that identifies sequential numbering for vendors and sets a field to '1' if present. I was thinking of running nested loops that first determine number of transactions per vendor then loops through those transactions comparing the transaction numbers. Can anybody help me with this??

To find one sequential set (2 records where one transaction number follows the other):

SELECT transactionId FROM tbl WHERE EXISTS 
  (SELECT * FROM tbl as t WHERE tbl.vendorId = t.vendorId 
   AND tbl.transactionId+1 = t.transactionId)

I'm not sure this is the most straightforward approach but I think it could work. Apologies for using multiple steps but Jet 4.0 kind of forces one to do so.**

I've assumed all transactionId values are positive integers and that a sequence is a set of evenly spaced transactionId values by vendorId. I further assume there is a key on (vendorId, transactionId).

First step, elmininate invalid rows e.g. need at least three rows to be able to determine a sequence (do all other rows pass or fail?); may want to filter other junk out here too (e.g. rows/groups with NULL values):

CREATE VIEW tbl1
AS
SELECT T1.vendorId, T1.transactionId
  FROM tbl AS T1
 WHERE EXISTS (
               SELECT T2.vendorId
                 FROM tbl AS T2
                WHERE T2.vendorId = T1.vendorId
                GROUP 
                   BY T2.vendorId
                HAVING COUNT(*) > 2
              );

Find the lowest value for each vendor (comes in handy later):

CREATE VIEW tbl2
AS
SELECT vendorId, MIN(transactionId) AS transactionId_min
  FROM tbl1
 GROUP 
    BY vendorId;

Make all sequences start at zero (transactionId_base_zero) by subtracting the lowest value for each vendor:

CREATE VIEW tbl3
AS
SELECT T1.vendorId, T1.transactionId, 
       T1.transactionId - T2.transactionId_min AS transactionId_base_zero
  FROM tbl1 AS T1
       INNER JOIN tbl2 AS T2
          ON T1.vendorId = T2.vendorId;

Predict the step value (difference between adjacent sequence values) based on the MAX, MIN and COUNT set values for each vendor:

CREATE VIEW tbl4
AS
SELECT vendorId, 
       MAX(transactionId_base_zero) / (COUNT(*) - 1)
          AS transactionId_predicted_step
  FROM tbl3;

Test that the predicted step value hold true for each squence value i.e. (pseudo code) this_transactionId - step_value = prior_transactionId (omit the lowest transactionId because it doesn't have a prior value!):

SELECT DISTINCT T.vendorId
  FROM tbl3 AS T
 WHERE T.transactionId_base_zero > 0
       AND NOT EXISTS (
                       SELECT *
                         FROM tbl3 AS T3
                              INNER JOIN tbl4 AS T4
                                 ON T3.vendorId = T4.vendorId      
                        WHERE T.vendorId = T3.vendorId
                              AND T.transactionId_base_zero 
                                     - T4.transactionId_predicted_step 
                                        = T3.transactionId_base_zero
                      );

The above query should return the vendorId of vendors whose transactionId values are not sequential.

** In my defense, I ran into a couple of bugs Jet 4.0 I had to code around workaround. Yes, I do know the bugs are in Jet 4.0 (or its OLE DB provider) because a) I double checked results using SQL Server and b) they defy logic! (even SQL's own strange 3VL logic :)

I would use a query that finds gaps in numbering for any vendor, and if that returns any records, then you do not have sequential numbering for all vendors.

SELECT *
FROM tblTransaction As T1
WHERE (
    SELECT TOP 1 T2.transactionID
    FROM tblTransaction As T2
    WHERE T1.vendorID = T2.vendorID AND
          T1.transactionID < T2.transactionID
    ORDER BY T2.transactionID
) - T1.transactionID > 1

What this does is, for each record in the table, look for the lowest-numbered other transactionID in the same table that is for the same vendor and has a higher-numbered transactionID than the first one. If that the transactionID value of that record is more than one higher than the value in the first record, that represents a gap in numbering for the vendor.

Edit: Changed variable names above as requested.