Results 1 to 4 of 4
  1. #1
    marles2 is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    2

    Identify duplicate data across multiple fields

    I have two orders with the same items to execute on the same date, which in my scenario means I have duplicates. I am in need of a query that only outputs/identifies the two orders that have the same items and date. I have tried various grouping and sorting queries but none yield the result I need. Any help or suggestions are appreciated.



    Example Table
    Order Item Date
    32586 4566 2/1/11
    32586 1456 2/1/11

    45634 4566 2/1/11
    45634 1456 2/1/11

    67894 4566 3/15/11
    67894 1456 3/15/11

    Result Looking for some kind of output like below

    Date Orders
    2/1 32586=45634

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    the right side of the = sign that you show doesn't make sense, given the rest of the data.

    but the wizard-generated 'find duplicates' query can do most of this. IDK if that's what you need or not, but give it a try if haven't already. In your example, a query like that would produce something like this:

    Code:
    date     item     numberofdups
    2/1      1456     2
    2/1      4566     2
    if you need something more sophisticated, such as a comparison of all piece of data on each order that could've been duplicated, you may need to use recordsets.

  3. #3
    marles2 is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    2
    Thanks for the reply, very helpful and it gets me closer to what I am looking for.

    Is there a way that would compare the two order numbers and their data against one another for duplicates, with the resulting output showing the order numbers in addition to the other field of data?

    Sounds like recordsets may be an option, any others out there?

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by marles2 View Post
    Is there a way that would compare the two order numbers and their data against one another for duplicates, with the resulting output showing the order numbers in addition to the other field of data?
    you know, I'm not sure. what it looks like to me is that you're using some sort of 'detail' table to do this analysis. it's so hard with that kind of table because you simply need to comb every record for each order number and compare it to every other record in every other order, to truly find if all = all, right?? I don't think the wizard can do this, really. I hope someone else can follow up on that, or prove me wrong.

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

Similar Threads

  1. Replies: 1
    Last Post: 11-11-2010, 11:00 AM
  2. Duplicate data fields
    By watergal82 in forum Access
    Replies: 1
    Last Post: 09-07-2010, 02:48 PM
  3. Multiple Fields Same Data
    By cassidym in forum Database Design
    Replies: 2
    Last Post: 08-03-2010, 12:28 PM
  4. Query to identify sequences of data
    By TheWolfster in forum Queries
    Replies: 13
    Last Post: 05-25-2010, 12:55 AM
  5. Replies: 3
    Last Post: 12-10-2009, 02:16 PM

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