Combine multiple entries and values

[linkerschuh]

Dear all,

I have an issue: I have a table which contains an identifier and then several characteristics in different fields. The problem is that the identifier is not unique and the identifier repeats.

Example:


Identifier

USA

Chile

Germany

UK



1

X


X




2


X


X



3

X






2

X






3



X





I am looking for code which "cleans" the table, so which combines the criteria behind an identifier and takes out unnecessary lines.

Example after "clean-up":


Identifier

USA

Chile

Germany

UK



1

X


X




2

X

X


X



3

X


X





I have tried several approaches, but I don't get it done.

Thanks for suggestions. It really freaks me out.

[jonh]

How are the records added?
The id field should be a primary key that doesn't allow duplicates - but if you change the table design you might encounter problems with data entry.

[linkerschuh]

How are the records added?
The id field should be a primary key that doesn't allow duplicates - but if you change the table design you might encounter problems with data entry.

Hi,

that's exactly the issue. Data is imported from three different sources and combined in the table. And all three sources can contain the same ID, but then different underlying characteristics. To stay with the earlier example, if source A contains for ID nr 1 only a cross in US, it can very well be that source nr 2 contains a cross in Chile and source nr 3 contains a cross in Germany. Because of this, it's also not possible to make ID the primary key, so that it is unique.

What at the end of the day should be in the table is one line for ID nr 1, with a cross in US, Chile and Germany. I tried around with SQL, but for some reason I simply don't get it done.

Thanks a lot for your help!

[jonh]

Are you saying the field values actually are X's?
You can't group rows of text unless the values are identical. You can group numeric values by summing so convert X to 1 sum the rows and convert anything not 0 back to X.

[linkerschuh]

Are you saying the field values actually are X's?
You can't group rows of text unless the values are identical. You can group numeric values by summing so convert X to 1 sum the rows and convert anything not 0 back to X.

The IDs are a letter/number combinations. The field values are X. They are all identical.

I have to say that I don't immediately see how converting the X's to numbers helps. Could you maybe give an example?

Thanks so much for your help!

[jonh]

I'll look into it. Why are you using X's and not booleans?

[linkerschuh]

There's no specific reason for the X's. That X's are assigned as part of the import function from the three different sources mentioned earlier. It could also be numbers, dashes, etc. Whatever works best to achieve the desired result.

Thanks again for looking into this!

[jonh]

Ok, one more question. Do you need this table updating with unique records or will a new table do?

[linkerschuh]

It can be both. So a new table which contains the "cleaned-up" datasets or an update of the existing table. Whatever is easier. When trying around I focused more on the solution with a new table because I thought it would be easier, but didn't get it to work.

Thanks again!

[jonh]

Ok, here you go. This query should display the values grouped and you can easily turn it into a make table query if you wish...


SELECT
Identifier,
Sum(Not IsNull([USA])) AS USA_,
Sum(Not IsNull([Chile])) AS Chile_,
Sum(Not IsNull([Germany])) AS Germany_,
Sum(Not IsNull([UK])) AS UK_
FROM Table1
GROUP BY Identifier

[linkerschuh]

Works well. Thanks a lot for your help!