# How to search for duplicate combinations

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. 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?

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.

-- Evan

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. 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.

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

#### 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