Look through several columns of a record (1 Viewer)

[w11184]

I would like to compare the values of several columns in the same record and remove any duplicates. So...

ID | Col1 | Col2 | Col3 | Col4 | Col5
-------------------------------------------
1 | A | B | C | D | C
2 | C | C | C | D | D
3 | A | A | B | A | D

In the above example I would remove one of the Cs in row 1.
Two Cs and one D in row 2 and two As in row 3.

At the moment I have the value of each column stored in a variable and each one is compared against each other using LOTS of If statements. Obviously this is not the most elegant solution so it would be great if you can point me towards a better solution.

Much thanks!

 

[sensetech]

You could use the Fields collection indexed by field numbers in a double loop to pick up the contents of each field and compare it against the contents of each subsequent field, zapping any subsequent matching fields. Something like this:

set td = currentdb.tabledefs("tableName")
set rs = currentdb.openrecordset("tableName")

while not rs.eof

rs.edit


for int1 = 0 to td.fields.count - 2

for int2 = int1 to td.fields.count - 1

if rs.field(int2) = rs.field(int1) then

rs.field(int2) = ""

end if

next int2

next int1

rs.update

rs.movenext

wend

rs.close


I accept no blame for syntax errors!!!

(Code was indented when I typed it in, honest!)

 

[The_Doc_Man]

W11184: There is an inherent problem in something you said here, though.

If this is a model of something you need to do in your business or school application, and it is legit to remove the contents of a column, I have to wonder if your data set is not fully normalized. Let's be clear - it is YOUR problem so only YOU know the correct representation of your data. However, if the operation you described is legit, then there is some underlying relationship between the columns that exists other than the record's prime key. If that is true, then this perhaps should be stored in a parent/child one/many structure.

If I am correct, then SenseTech's answer is not entirely wrong but would have to be restructured by having the parent/child relationship and a more complex data representation.

 

[w11184]

Thanks for the reply.

The Doc Man: I completely agree about the data not being normalised. It is unfortunately a limitation in the way the dataset is exported from the original database and what I am doing now is simply trying to carry out some cleaning but in an automised way.

Sensetech: I think I kind of understand what your code is doing but could you just explain briefly what it is trying to achieve? Apologies for my slowness.

 

[sensetech]

Using record ID 1 from your data example:

The outer FOR loop first looks at Col1 (well, it would if I'd started at 1 instead of 0!) which has value A, then the inner FOR loop cycles through Col2, Col3, Col4 and Col5 (but should have started at 2, not 1), zapping any of them which match Col1 - there aren't any.

The outer FOR loop then looks at Col2 which has value B, then the inner FOR loop cycles through Col3, Col4 and Col5, zapping any of them which match Col1 - there aren't any.

Ditto the outer loop for Col3 checked against Col4 and Col5 then Col4 checked against just Col5.

You need a basic understanding of the Fields collection to fully understand what it's doing.

(PS I can give mini-lectures on data analysis too, if required!)

 

[Galaxiom]

I completely agree about the data not being normalised. It is unfortunately a limitation in the way the dataset is exported from the original database and what I am doing now is simply trying to carry out some cleaning but in an automised way.

Generally the best solution in these situations is to use queries to normalize the data as the first step.

Then cleaning it up becomes simple.