how to delete duplicates and combine values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

how to delete duplicates and combine values

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-02-03 : 08:18:49

Michael writes "Hello,

I am trying to combine data into a single cell in a data table and delete duplicates. This sounds confusing so I will give you an example of what I am trying to accomplish. I am working with acronyms.

Sample table 1:

AB Al Ben
AB Atlanta Bombers
AB Aztec Brazil

I need this table with 2 columns and 3 rows to look like this when I am done

AB Al Ben, Atlanta Bombers, Aztec Brazil

So it lists out all possible meanings of AB next to AB in the same row and deletes the other rows. There will also be rows that have only one single meaning like this

AB Atlanta Bombers
AZ Arizona Zebras
BC Baltimore Coolers

those are all fine, just combine the ones which have more than one meaning. Thank you so much in advance for the help. I have gotten this to work in excel, can not get it to work in access. Thanks again."

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-03 : 13:08:07

quote:
can not get it to work in access. Thanks again

I think you need post this in the MS Access forum

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-04 : 06:45:32

Somewhat tough in Access

Any way try this

select * into temptable from (
Select * from (
Select Distinct a,b ,(select count(*) from Test T1 where T1.A=T2.A and T1.b<=T2.B) as Sno from test T2 ) t)

then

Select Distinct A+' '+One+' '+ two+' ' +iif(isnull(three),'',three) as Result from (
select A, (Select B from temptable T1 where t1.A=T2.A and T1.Sno=1) as one
,(Select B from temptable T1 where t1.A=T2.A and T1.Sno=2) as two ,(Select B from temptable T1 where t1.A=T2.A and T1.Sno=3) as three from temptable t2
) t

Madhivanan

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-04 : 08:46:11

so, you want to take a normalized database and unnormalized it, stuffing multiple values into 1 column? Or is this an output/presentation issue -- you want to take you existing data and generate a report or a query that formats the data that way?

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources