Combining three fields into one

[Layth]

I have three fields in a query that each give a decimal amount, the three are unique meaning if one is not null, the other two are null.

What I'm trying to do is place all three of these fields into one field I'll show an example

City Code 1 Code 2 Code3
San Juan 45.4
Lansing 35.12
Rapid City 65.4
Salem 41.54



What I want is:

City Codes
San Juan 45.4
Lansing 35.12
Rapid City 65.4
Salem 41.54



Here is my SQL code it only gives me the results of the initial query and not the two sub queries.

SELECT *,
[subMasterpiece].[Total] AS CombinedTotal
FROM [subMasterpiece]
WHERE [subMasterpiece].[REGTOTAL] is NULL
AND [subMasterpiece].[PROTOTAL] is NULL
OR EXISTS
     (SELECT [subMasterpiece].[PROTOTAL] AS CombinedTotal
      FROM [subMasterpiece]
      WHERE [subMasterpiece].[Total] is NULL
      AND [subMasterpiece].[REGTOTAL] is NULL
      OR EXISTS
            (SELECT [subMasterpiece].REGTOTAL AS CombinedTotal
             FROM [subMasterpiece]
             WHERE [subMasterpiece].[Total] is NULL
             AND [subMasterpiece].[PROTOTAL] is NULL));

I think this code is the way to approach this problem, but I may be off. Any help is as always highly appreciated.

thanks in advance,

Layth

 

[Crowley16]

why not a union query?

select city, code1 from tbl

union select city, code2 from tbl

union select city, code3 from tbl

you can put where checks in the selects to only retrieve the non null records if they popup...

--------------------
Procrastinate Now!

 

[Layth]

I actually have fields in all three of the queries that are the same that I need to have in the final union as well, what happens to those fields

 

[Crowley16]

you mean some results are the same from tbl1 and tbl2?

then you'll get duplicate results in your final union query...

but you can then use a select distinct on this query to get rid of those...

--------------------
Procrastinate Now!

 

[PHV]

Something like this ?
SELECT city, Total AS CombinedTotal FROM subMasterpiece WHERE Total Is Not Null
UNION SELECT city, REGTOTAL FROM subMasterpiece WHERE REGTOTAL Is Not Null
UNION SELECT city, PROTOTAL FROM subMasterpiece WHERE PROTOTAL Is Not Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Layth]

Close, the city field was just used as an example the code actually unioning total prices, this is what ended up working for me. Also I had to tweak the queries a bit submasterpiece=MASTERPIECE, thanks for your help

SELECT *,
[MASTERPIECE].[Total] AS CombinedTotal
FROM [MASTERPIECE]
WHERE [MASTERPIECE].[REGTOTAL] is NULL
AND [MASTERPIECE].[PROTOTAL] is NULL
UNION
SELECT *, [MASTERPIECE].[PROTOTAL] AS CombinedTotal
FROM [MASTERPIECE]
WHERE [MASTERPIECE].[Total] is NULL
AND [MASTERPIECE].[REGTOTAL] is NULL
UNION SELECT *, [MASTERPIECE].REGTOTAL AS CombinedTotal
FROM [MASTERPIECE]
WHERE [MASTERPIECE].[Total] is NULL
AND [MASTERPIECE].[PROTOTAL] is NULL;

 

[PHV]

And why not simply this ?
SELECT *, Total AS CombinedTotal FROM MASTERPIECE WHERE Total Is Not Null
UNION SELECT *, PROTOTAL FROM MASTERPIECE WHERE PROTOTAL Is Not Null
UNION SELECT *, REGTOTAL FROM MASTERPIECE WHERE REGTOTAL Is Not Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886