Querying From two records in a table and appending to single record

[stazza18]

Our company has various accounts for our customers. We have to run a process where we must pull all of these accounts into a single table and perform the calculations we need. For example one table has a customer account 1, customer account 2, and customer account 3. We must pull all of these various accounts and append them to one account in a table we made. As the system exists currently, this process is done in 3 seperate queries. I have to go in and make the system run smoother and I was wondering if there was any way to do this in one SQL statement. I tried compacting the query but I cannot get it to work without the AND statement, which only ends up pulling all of the accounts that are found in all of the records. Is it possible to accomplish what I want?

 

[SkipVought]

stazza18,

Your problem is a result of a poor, non-normalized database design. Oddly enough, you NORMALIZE, at least on Account, in order to be able to process.

Here's a method of querying in "one swell foop!" ;-)
[tt]
Select Customer, Account1
From MyTable
Union
Select Customer, Account2
From MyTable
Union
Select Customer, Account3
From MyTable
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[stazza18]

Thanks very much. I didn't even think to do it this way, I kept trying to pull and append at the same time.