Results 1 to 6 of 6

How to search for duplicate combinations

  1. #1
    EvanRosenlieb is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Location
    Fort Collins, CO
    Posts
    37

    How to search for duplicate combinations

    Hello all,

    I need to be able to find duplicate combinations (or, I suppose, permutations). I have four columns in a table that all contain inter values. I want to be able to query so that I can see if there are more than one records that have, say, 12, 28, 5, 14 in columns 1, 2, 3, and 4, respectively. Does anyone know of a way to do this?

    --Evan

  2. #2
    dave_m is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Apr 2012
    Location
    San Francisco
    Posts
    4
    Do you only consider it a duplicate if the order is the same, or is 12,28,5,14 a duplicate of 28,5,12,14? Do you have a primary key on this table?

  3. #3
    EvanRosenlieb is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Location
    Fort Collins, CO
    Posts
    37
    Dave-

    Yes there is a primary key.

    The other integers over which I want to find duplicates are ID's for other tables, and because of it, I actually need to find permutations rather than combinations. This is because having a 12, 28, 5, 14 in columns 1, 2, 3, and 4 respectively means something complete different than 28, 12, 14, 5 in columns 1, 2, 3, and 4 respectively. I hesitate to say that "Order matters" as the order that columns 1, 2, 3 and 4 appear is arbitrary, but I think you get the idea.

    Thanks for the reply,

    -- Evan

  4. #4
    EvanRosenlieb is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Location
    Fort Collins, CO
    Posts
    37
    Alright, I am doing some research on this, and it appears I need to be more clear.

    The purpose of this specific table is a look up table like, say, a Students-T table. It is a way of relating abstract theoretical data to actual observed data (which most of the database is dedicated to). We have a theoretical model that gives us a value of some Independent Variable, IV, as a function of four Dependent Variable, DV1, DV2, DV3, DV4, notationally: IV=f(DV1, DV2, DV3, DV4). We can observe four of these variables in the field. Unfortunately, the four values we can observe are DV1, DV2, DV3, and IV. We would like to use these four variables to deduce the last (DV4). The easiest way to do this, of course, would be to solve the formula for DV4. Unfortunately this is mathematically impossible due to the models heavy reliance of transcendental functions. So, in order to find values for DV4 as a function of observed values of DV1, DV2, DV3, and IV, we used a mathematical software program to generate values of IV for values of DV1, DV2, DV3, and DV4 that fit the range of observed/expected values. Now that we have a whole table that has values for each of the five variables in each record, it should theoretically be possible to use it to find values of DV4 from having the other four values.

    So anyway, this whole table was imported into Access, and I had access add a primary key in the form of the autonumber field. In doing research to solve this problem, I have discovered about Composite Primary Keys, which I didn't know existed before. After learning about composite primary keys, it turns out thats exactly what I need in the table. Although I have an autonumber field set as the primary key, what I really should have is a composite Primary Key that is comprised out of DV1ID, DV2ID, DV3ID, and IVID. The autonumber field is not needed at all.

    But the catch is, I'm not sure if there is one and only one unique permutation of these four values in the table. I know that there is a one and only one unique permutation of the DV1, DV2, DV3, and DV4 values, because that is how we defined it when we generated the table. However, it occurred to me that, for instance, the IV value generated by DV1 = 12, DV2 = 28, DV3 = 5 and DV4 = 14, could be so close to the the IV value generated by DV1 = 12, DV2 = 28, DV3 = 5 and DV4 = 15 that they could have been placed in the same Bin, and thus have the same IVID. Lets say that both of these permutations of the dependent variable ended up generated an independent variable value that was in the bin that had IVID = 10. That means that we would have two records in the table that would have DV1 = 12, DV2 = 28, DV3 = 5, and IV = 10 (The last column, the one we are trying to use this table to find, would be different as one record would have DV4 = 14 and one DV4=15). Obviously, if I tried to set the primary key as a composite of DV1ID, DV2ID, DV3ID, and IVID, this would violate the rules of primary key, as there would be two identical "values" (which in this case are actually two identical permutations).

    So this gets back to the original question, how can check to see if there are any duplicate permutations so I know whether or not I can set the primary key to the composite of these four fields?

  5. #5
    MarvinP is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Seattle, WA
    Posts
    2
    Hi Evan,

    Would it make sense to build a new field in the table of the 4 values combined into a single sting?
    Add a new field to the table and then use an Update query to build the 4 value combination..
    Like
    DV1 & "-" & DV2 & "-" & DV3 & "-" & DV4.
    and use this to update the new field.

  6. #6
    EvanRosenlieb is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Location
    Fort Collins, CO
    Posts
    37
    Marvin,

    This is a very smart way to solve this problem, and thank you for the response, but honestly I did not even try!

    I actually found out the the Query wizard (in general, I never play around with the wizards, but I was desperate) that come pre-packaged in Access knows how to handle this situation.

    If you use the find duplicates wizard, in the window it allows you to select more than one field to search for duplicates. By choosing the four fields I was interested in, I was able to accomplish exactly what I was trying to do.

    Thank you for the responses, however!

    --Evan

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

Similar Threads

  1. Calculating all possible combinations
    By Data in forum Queries
    Replies: 3
    Last Post: 06-20-2011, 02:02 PM
  2. Need a query to figure all possible combinations
    By julestrip in forum Queries
    Replies: 1
    Last Post: 05-27-2011, 06:23 AM
  3. Query for All Possible Combinations
    By Rawb in forum Queries
    Replies: 1
    Last Post: 10-09-2010, 07:33 PM
  4. Multi-Query List Box Combinations?
    By BizIntelGuy in forum Access
    Replies: 3
    Last Post: 07-20-2010, 02:20 PM
  5. ms access version combinations
    By marianne in forum Access
    Replies: 1
    Last Post: 08-05-2009, 06:37 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
  •  
Tech Forums: Microsoft Office Forums