If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
comparing adjacent records - looking for dup/missing customer orders
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 |
#2
|
|||
|
|||
comparing adjacent records - looking for dup/missing customer orders
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Jesse" wrote in message ... 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 |
#3
|
|||
|
|||
comparing adjacent records - looking for dup/missing customer orders
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 "Jesse" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|