comparing adjacent records - looking for dup/missing customer orders

[Jesse]

I'd like to test/compare adjacent records to look for duplicate orders
and missing orders from my shopping cart data.

I am an Access 2000 newbie. I had these tests in my db when I used
Excel, but I don't know how to recreate them in Access.

My db has records imported from a shopping cart. The cart provides a
sequential order number -- adding 1 to the prior order number. Due to
ongoing software glitch issues, sometimes (if rarely) it skips a
number -- or, it doesn't skip a number but an order that exists does
not download. And, sometimes (for whatever reasons) a customer's order
is entered twice with different order numbers -- usually 1st order
number +1 or +2.

The order number is the "key" field for all transactions.

So, I want to test for missing order numbers and for possibly
duplicate orders. For each test field, if a test shows a problem, it
would yield a value of 1; else 0.

To test for duplicate orders, I'd like to compare values in two fields
in the current record (e.g., last_name and zip_cd) to the same field
values in the prior two records.

To test for missing orders, I'd like to take the current order number,
subtract 1, and see if that equals the order number of the prior
record.

Is there a straightforward way to do this? If so, advice and/or hints
would be appreciated. TIA. -- Jesse

 

[Allen Browne]

There are 2 parts to your question:
a) How to identify missing order numbers.
b) How to identify records with duplicate order numbers;

Use a subquery to get the order number from the previous record. The query
will be something like this:
SELECT OrderNum,
(SELECT Max(OrderNum) FROM Orders AS Dupe
WHERE Dupe.OrderNum < Orders.OrderNum) AS PriorOrderNum
FROM Orders;
Once you have that working, you can add criteria under the subquery like
this:
< [OrderNum] - 1

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

To identify records where the OrderNum occurs more than once, use a Totals
query:
SELECT OrderNum, Count(OrderNum) AS CountOfOrderNum
FROM Orders
GROUP BY OrderNum
HAVING Count(OrderNum) > 1;

More info about that:
Finding Duplicates in an imported DBF
at:
http://allenbrowne.com/xbase-04.html

BTW, if the OrderNum is the primary key in Access, then the duplicates will
fail on import.

 

[John Spencer]

Find Missing numbers
Assumptions
--The OrderID field is numeric.
--At most, only one number is missing in a sequence (that is 2 and 3 would
not both be missing after 1)

SELECT T1.OrderID - 1 as MissedThis
FROM YourTable as T1 LEFT JOIN YourTable as T2
ON T1.OrderID = T2.OrderID-1
WHERE T2.OrderID is Null

Finding duplicates based on LastName and ZipCode is possible but a bit more
complex. A simple query to get the first order that has duplicates in the
next 1 or 2 orderIds would probably look like

SELECT T1.OrderID as HasDuplicates
, T1.LastName
, T1.ZipCode
FROM YourTable as T1 INNER JOIN YourTable as T2
On T1.LastName = T2.LastName and T1.ZipCode = T2.Zipcode
WHERE T1.ORDERID = T2.OrderID+1 or T1.OrderID = T2.OrderID +2