Results 1 to 8 of 8
  1. #1
    Jeema is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2017
    Posts
    3

    duplicate orders

    I am trying to create a query that will return duplicate orders in my database. I consider an order to be potentially duplicate if it was made by the same customer, in the same month, the same products and the same quantity of products. For example, this is an extract from my orders table:



    OrderID OrderDate ProductID ProductQuantity
    1001 1/1/17 2001 4
    1001 1/1/17 2002 5
    1001 1/1/17 2003 6
    1002 1/15/17 2001 4

    I created a duplicate query that checks that OrderDate (Month(OrderDate)), the ProductID and ProductQuantity for duplicate entries. The problem I faced is that the query checks the duplicates in each row and the order is comprised of multiple rows. So if a customer purchased a number of products in a month and then made another order in the same month and one of the products has the same quantity as the previous order then the query will return a match. For example the query will consider order 1002 in the above table a duplicate to order to 1001 because ProductID 2001 has the same quantity. How can I make the query consider the whole order not only a single row of the order ?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    You need to clarify what you mean by

    How can I make the query consider the whole order not only a single row of the order ?
    Provide some example data of what should be included, what shouldn't be included and why. At the moment you seem to be saying that 1002 should not be considered a duplicate because the other record is part of another order which has additional different products.

    Also, please provide your actual data structure - as presented, your data is not normalised. I would expect as a minimum 2 tables, one for the order header and one for order lines.

  3. #3
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    This is not accomplished in a single query. Your sample display does not include key fields or the customer ID field - so any explanation is necessarily generic. Your first query is to identify Suspects. Save that query. I will guess it might include Customer ID, Month, Product (not sure).

    Then you use the Suspects query to join to your table to create another query that will refine or refute. I can't describe in more precision without knowing the data structure. You may indeed need a 3rd or 4th query.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Jeema,

    As the others have said, your data structure does not match your business.
    A major design issue is to NOT have duplicates in your tables.

    Typically, when dealing with Customer, Order and Product you would have this general set up.
    This is from a previous response to another poster-- you can ignore the field TotalSquare

    Click image for larger version. 

Name:	CustOrderProducts.jpg 
Views:	11 
Size:	27.2 KB 
ID:	27607

    Here is another free data model from Barry Williams' site.

    Good luck.

  5. #5
    Jeema is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2017
    Posts
    3
    Thanks Ajax, NTC, orange for your replay.
    It seems I have made a train wreck with my first post on this forum, my apologies.
    Let me try again,

    Products Table

    ProductID ProductName
    2001 Classic
    2002 Economy
    2003 Business

    Customers Table

    CustomerID CustomerName
    3001 Red
    3002 Blue
    3003 Green

    Orders Table

    OrderID OrderDate CustomerID
    4001 1/1/2017 3001
    4002 1/15/2017 3001

    Orders Detail

    OrderID ProductID Quantity
    4001 2001 20
    4001 2002 25
    4001 2003 30
    4002 2001 20
    Next I create this query

    OrderID Month(OrderDate) CustomerID ProductID Quantity
    4001 1 3001 2001 20
    4001 1 3001 2002 25
    4001 1 3001 2003 30
    4002 1 3001 2001 20

    Now, as I said in my first post, an order is considered potentially duplicate if it was made by the same customer, in this same month, and the same products with same quantities (as to why this is considered potentially duplicate, because of certain business concerns).

    As you can see from the query that I have made, I have two orders (4001 and 4002). If I create a duplicate query from the query above, it will return the first row and last row as duplicates (OrderID will not be included in the duplicate query). So as you can see, the OrderID No. 4001 comprised from three rows, but the duplicate query returned Order 4001 and 4002 duplicate because it checks one row a time and does not consider the whole order.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    can you add to this data to show what you mean - as before I think you are saying you don't consider first and last line to be a duplicate because the first line has additional items in the order but you would consider this to be a duplicate because all rows match, even if in a different order (apologies for layout). So far you have told us what is not a duplicate, but not demonstrated what is a duplicate

    OrderID Month(OrderDate) CustomerID ProductID Quantity
    4001 1 3001 2001 20
    4001 1 3001 2002 25
    4001 1 3001 2003 30
    4002 1 3001 2001 20

    4002 1 3001 2003 30
    4002 1 3001 2002 25

  7. #7
    Jeema is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2017
    Posts
    3
    Ajax,

    A duplicate Order would be something like this

    OrderID Month(OrderID) CustomerID ProductID Quantity
    4003 3 3002 2001 20
    4003 3 3002 2002 30
    4004 3 3002 2001 20
    4004 3 3002 2002 30


    Orders 4003 & 4004 are potentially duplicate orders, same month, same customer, same products, same quantities.

  8. #8
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    Assuming starting data set fields ;
    OrderID Month(OrderDate) CustomerID ProductID Quantity
    4001 1 3001 2001 20
    4001 1 3001 2002 25
    4001 1 3001 2003 30
    4002 1 3001 2001 20
    4003 3 3002 2001 20
    4003 3 3002 2002 30
    4004 3 3002 2001 20
    4004 3 3002 2002 30

    one makes the following queries:

    Dupe1: aggregate query, all columns set to Group On, EXCEPT Order ID which you change it to Count, set criteria of OrderID Count to >1;

    Dupe2: using Dupe1, and initial starting record set – join all fields of Dupe1 to record set (NOT the OrderID field - do not join this); in query design only use fields of record set do not include fields of Dupe2

    At this point these are your duplicate CANDIDATES – but can show false positives in terms of an overall order because so far this analysis is at the individual record level
    i.e. where a customer orders twice with 1 (or more) records identical but other records are not – so that the order itself is not duplicate but individual records happen to be - this will show on this list. This situation may or may not be sufficient volume that the admin person can manage the review manually.

    To filter out further to establish the overall order duplication, one must extend this concept via a second set of queries
    Last edited by NTC; 02-25-2017 at 05:47 PM.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-01-2016, 09:04 AM
  2. Relationship between Orders and Customers
    By jongar in forum Database Design
    Replies: 2
    Last Post: 05-14-2015, 07:40 AM
  3. Rooms and orders
    By Seldek in forum Forms
    Replies: 1
    Last Post: 12-08-2012, 09:38 PM
  4. Purchase orders 2 in 1
    By jordanturner in forum Forms
    Replies: 1
    Last Post: 09-09-2010, 03:38 AM
  5. Orders & Products
    By mastromb in forum Database Design
    Replies: 4
    Last Post: 01-22-2010, 07:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums