Transpose Data from Row to column format help needed

[cadeucsb]

I have a table of account number data. The table includes a field for Account Number and a field for Master Account Number.

The master account number is an aggregator of account numbers so we know which account numbers go together for one vendor/client/etc.

The problem I have is an account can have 2 master account numbers. So essentially this account number will be in my table twice (once for each master#).

My hope is to get each account number into the table once with an additional Master Account #2 field. I have been racking my brain and cant think of a way to execute it.

any help is greatly appreciated.

[ketbdnetbp]

A few questions??

You stated: The master account number is an aggregator of account numbers so we know which account numbers go together for one vendor/client/etc.

Question: What do you mean by, "aggregator of account numbers"?
Is this a series of numbers separated by a comma or semi-colon, etc. OR is this field based on logic or a particular formula OR is it something else?

Further, you also stated: The problem I have is an account can have 2 master account numbers. Question: Why and when does this occur?

Once this information is known, more specific help can be provided.

All the best,

Jim

[cadeucsb]

A few questions??

You stated: The master account number is an aggregator of account numbers so we know which account numbers go together for one vendor/client/etc.

Question: What do you mean by, "aggregator of account numbers"?
Is this a series of numbers separated by a comma or semi-colon, etc. OR is this field based on logic or a particular formula OR is it something else?

Further, you also stated: The problem I have is an account can have 2 master account numbers. Question: Why and when does this occur?

Once this information is known, more specific help can be provided.

All the best,

Jim

With the data, it is a set table of info. No logic or formula or csv loads etc. its an existing table with fields: Account Number and Master Account Number.

To further explain the data...

lets say youre a client with 5 account numbers: 1,2,3,4 and 5.
Now on top of that we would have 1 master account number: 99.

Accounts 1,2,3,4,5 are all under this 99 Master account so we know which 5 belong to you. So if someone asks, which are Jim's accounts, I dont have to get a list of all 5 accounts numbers, I can just say Master Account 99.

See the attached to better explain the current data layout and what I am trying to do.

cade

[cadeucsb]

anyone else have thoughts on this? been manually dealing with this for years and would love to fix it on the db table side.