Transposing duplicate rows to new columns

[m.wanstall]

Hi there,

I have a large table based around a CustomerNumber with various details
about that customer (originally imported and augmented from an Exchange
Server)...now, a single customer can have many email addresses and thus
will have multiple entries in this table but this isn't what I want. Is
there a way to relatively simply take any duplicate CustomerNumber
records, take their EmailAddress field and append it in a new column
(EmailAddressN as applicable)? I wasn't sure whether some sort of
Crosstab query had some advanced functionality making this a
possibility.

Any help is greatly appreciated as I'm sure someone would have come
past this problem before!

Thanks,
-Mal

[David F Cox]

Yes it is possible, but I do not think that you should do that, it violates
the principles of a relational database and will cause you many unnecessary
difficulties down the line.

I recommend that you put the email address in a new table with the customer
number as the key. A simple make table query will do that.

"m.wanstall" <m.********@gmail.comwrote in message
news:11**********************@16g2000cwy.googlegro ups.com...

Hi there,

I have a large table based around a CustomerNumber with various details
about that customer (originally imported and augmented from an Exchange
Server)...now, a single customer can have many email addresses and thus
will have multiple entries in this table but this isn't what I want. Is
there a way to relatively simply take any duplicate CustomerNumber
records, take their EmailAddress field and append it in a new column
(EmailAddressN as applicable)? I wasn't sure whether some sort of
Crosstab query had some advanced functionality making this a
possibility.

Any help is greatly appreciated as I'm sure someone would have come
past this problem before!

Thanks,
-Mal

[m.wanstall]

Thanks David,

Sorry I should have said, I realise normalization of the data would be
the ideal solution BUT this is imported directly from an Exchange
server which is updated VERY regularly so I was hoping to cut down on
the amount of data separation since that is part of the problem with
the current system. I realise normalization and proper relationships is
the ideal solution however I am working in a less than ideal
environment and this database is piggybacking on a fair few dynamic and
unintuitive systems thus why I shy away from any further normalization.

Despite the trade off, could you possibly explain how I could do this
transposition via a query?

Many thanks,
-Mal

David F Cox wrote:

Yes it is possible, but I do not think that you should do that, it violates
the principles of a relational database and will cause you many unnecessary
difficulties down the line.

I recommend that you put the email address in a new table with the customer
number as the key. A simple make table query will do that.

"m.wanstall" <m.********@gmail.comwrote in message
news:11**********************@16g2000cwy.googlegro ups.com...

Hi there,

I have a large table based around a CustomerNumber with various details
about that customer (originally imported and augmented from an Exchange
Server)...now, a single customer can have many email addresses and thus
will have multiple entries in this table but this isn't what I want. Is
there a way to relatively simply take any duplicate CustomerNumber
records, take their EmailAddress field and append it in a new column
(EmailAddressN as applicable)? I wasn't sure whether some sort of
Crosstab query had some advanced functionality making this a
possibility.

Any help is greatly appreciated as I'm sure someone would have come
past this problem before!

Thanks,
-Mal

[David F Cox]

The universe is a "less than ideal" environment, and it is people sitting in
your seat making your decisions that help keep it so. I have been there and
done that, and feel sorry for all of those that have had to clear up my
mess. I do not think I will be doing you, or anyone following you, any
favours if I get you through the poison ivy into the haunted forest. Sorry.

I really think that you should do the work now to save the work later and
automate the process of getting data into the database in proper fashion. I
would love to help with that, but I really do not have the time at present.

"If you are going through hell ...
.... keep going." Sir Winston Churchill

"m.wanstall" <m.********@gmail.comwrote in message
news:11**********************@73g2000cwn.googlegro ups.com...

Thanks David,

Sorry I should have said, I realise normalization of the data would be
the ideal solution BUT this is imported directly from an Exchange
server which is updated VERY regularly so I was hoping to cut down on
the amount of data separation since that is part of the problem with
the current system. I realise normalization and proper relationships is
the ideal solution however I am working in a less than ideal
environment and this database is piggybacking on a fair few dynamic and
unintuitive systems thus why I shy away from any further normalization.

Despite the trade off, could you possibly explain how I could do this
transposition via a query?

Many thanks,
-Mal

David F Cox wrote:

>Yes it is possible, but I do not think that you should do that, it
violates
the principles of a relational database and will cause you many
unnecessary
difficulties down the line.

I recommend that you put the email address in a new table with the
customer
number as the key. A simple make table query will do that.

"m.wanstall" <m.********@gmail.comwrote in message
news:11**********************@16g2000cwy.googlegr oups.com...

Hi there,

I have a large table based around a CustomerNumber with various details
about that customer (originally imported and augmented from an Exchange
Server)...now, a single customer can have many email addresses and thus
will have multiple entries in this table but this isn't what I want. Is
there a way to relatively simply take any duplicate CustomerNumber
records, take their EmailAddress field and append it in a new column
(EmailAddressN as applicable)? I wasn't sure whether some sort of
Crosstab query had some advanced functionality making this a
possibility.

Any help is greatly appreciated as I'm sure someone would have come
past this problem before!

Thanks,
-Mal

[m.wanstall]

Fair call David and I understand where you're coming from. The most
pressing factor in my position, as you would know, is time (or lack
there of). Whilst I would sleep better at night if I could do this the
"right" way, it may simply be a matter of time. However thanks for your
opinion I do certainly understand your point of view, I know 90% of my
job is fixing up the legacy mess others left me and it would seem
counter productive for me to keep the wheel turning...but therein lies
one of the things I have grown to severely dislike about multinational
enterprise systems.

I'll see if I can help us both sleep a little better or at least try to
find some middle ground.
David F Cox wrote:

The universe is a "less than ideal" environment, and it is people sitting in
your seat making your decisions that help keep it so. I have been there and
done that, and feel sorry for all of those that have had to clear up my
mess. I do not think I will be doing you, or anyone following you, any
favours if I get you through the poison ivy into the haunted forest. Sorry.

I really think that you should do the work now to save the work later and
automate the process of getting data into the database in proper fashion. I
would love to help with that, but I really do not have the time at present.

"If you are going through hell ...
... keep going." Sir Winston Churchill

"m.wanstall" <m.********@gmail.comwrote in message
news:11**********************@73g2000cwn.googlegro ups.com...

Thanks David,

Sorry I should have said, I realise normalization of the data would be
the ideal solution BUT this is imported directly from an Exchange
server which is updated VERY regularly so I was hoping to cut down on
the amount of data separation since that is part of the problem with
the current system. I realise normalization and proper relationships is
the ideal solution however I am working in a less than ideal
environment and this database is piggybacking on a fair few dynamic and
unintuitive systems thus why I shy away from any further normalization.

Despite the trade off, could you possibly explain how I could do this
transposition via a query?

Many thanks,
-Mal

David F Cox wrote:

Yes it is possible, but I do not think that you should do that, it
violates
the principles of a relational database and will cause you many
unnecessary
difficulties down the line.

I recommend that you put the email address in a new table with the
customer
number as the key. A simple make table query will do that.

"m.wanstall" <m.********@gmail.comwrote in message
news:11**********************@16g2000cwy.googlegro ups.com...
Hi there,

I have a large table based around a CustomerNumber with various details
about that customer (originally imported and augmented from an Exchange
Server)...now, a single customer can have many email addresses and thus
will have multiple entries in this table but this isn't what I want. Is
there a way to relatively simply take any duplicate CustomerNumber
records, take their EmailAddress field and append it in a new column
(EmailAddressN as applicable)? I wasn't sure whether some sort of
Crosstab query had some advanced functionality making this a
possibility.

Any help is greatly appreciated as I'm sure someone would have come
past this problem before!

Thanks,
-Mal