| Author |
 Topic  |
|
|
sergeant_time
Yak Posting Veteran
68 Posts |
 Posted - 10/12/2012 : 10:32:00
|
I created a union query for a list type control for one of my reports. I would like to update this queary to combine the FPCC and CCP vice listing them seperately. Below is copy of the union queury that I used to create my list.
SELECT '8' AS Split, 'FPCC' AS PilotName, 1 AS OrderID
UNION
SELECT '28' AS Split, 'FPCC' AS PilotName, 1 AS OrderID
UNION
SELECT '3' AS Split, 'CCP' AS PilotName, 1 AS OrderID
UNION
SELECT '19' AS Split, 'PCC VISN 17' AS PilotName, 4 AS OrderID
UNION
SELECT '43' AS Split, 'PCC VISN 4' AS PilotName, 5 AS OrderID
UNION
SELECT '44' AS Split, 'PCC VISN 6' AS PilotName, 6 AS OrderID
UNION
SELECT '45' AS Split, 'PCC VISN 2' AS PilotName, 7 AS OrderID
UNION
SELECT '46' AS Split, 'PCC VISN 1' AS PilotName, 8 AS OrderID
UNION
SELECT '47' AS Split, 'PCC VISN 22 LONG BEACH' AS PilotName, 9 AS OrderID
UNION
SELECT '49' AS Split, 'PCC VISN 11- Danville' AS PilotName, 11 AS OrderID
UNION
SELECT '66' AS Split, 'PCC VISN 22 LOMA LINDA' AS PilotName, 12 AS OrderID
ORDER BY OrderID
|
Edited by - sergeant_time on 10/12/2012 12:02:12
|
|
|
lazerath
Constraint Violating Yak Guru
USA
278 Posts |
|
|
lazerath
Constraint Violating Yak Guru
USA
278 Posts |
Posted - 10/12/2012 : 12:47:57
|
| I see you edited your existing post, but did not provide the expected output. In order for us to help you, it is important that you make an attempt to help us. |
 |
|
|
sergeant_time
Yak Posting Veteran
68 Posts |
Posted - 10/16/2012 : 10:19:05
|
I would like to combine the 'CPP' with 'FPCC' into one.
SELECT '8' AS Split, 'FPCC' AS PilotName, 1 AS OrderID
UNION
SELECT '3' AS Split, 'CCP' AS PilotName, 1 AS OrderID
UNION
SELECT '19' AS Split, 'PCC VISN 17' AS PilotName, 4 AS OrderID
UNION
SELECT '43' AS Split, 'PCC VISN 4' AS PilotName, 5 AS OrderID
UNION
SELECT '44' AS Split, 'PCC VISN 6' AS PilotName, 6 AS OrderID
UNION
SELECT '45' AS Split, 'PCC VISN 2' AS PilotName, 7 AS OrderID
UNION
SELECT '46' AS Split, 'PCC VISN 1' AS PilotName, 8 AS OrderID
UNION
SELECT '47' AS Split, 'PCC VISN 22 LONG BEACH' AS PilotName, 9 AS OrderID
UNION
SELECT '49' AS Split, 'PCC VISN 11- Danville' AS PilotName, 11 AS OrderID
UNION
SELECT '66' AS Split, 'PCC VISN 22 LOMA LINDA' AS PilotName, 12 AS OrderID
ORDER BY OrderID
|
|
|
|
lazerath
Constraint Violating Yak Guru
USA
278 Posts |
Posted - 10/17/2012 : 16:13:20
|
| Provide a [SELECT ... UNION ...] statement that will produce the result you want. There are many ways to combine things and combining the way to "combine 'CPP' with 'FPCC'" is not obvious. Do you want a comma separated string like 'CPP,FPCC'? How do you want to reconcile the Split field? Is that supposed to be added together? Also comma separated? MIN / MAX? |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3826 Posts |
Posted - 10/17/2012 : 16:33:05
|
here I put them together for ya:SELECT '83' AS Split, 'FPCCCCP' AS PilotName, 1 AS OrderID
UNION
SELECT '19' AS Split, 'PCC VISN 17' AS PilotName, 4 AS OrderID
UNION
SELECT '43' AS Split, 'PCC VISN 4' AS PilotName, 5 AS OrderID
UNION
SELECT '44' AS Split, 'PCC VISN 6' AS PilotName, 6 AS OrderID
UNION
SELECT '45' AS Split, 'PCC VISN 2' AS PilotName, 7 AS OrderID
UNION
SELECT '46' AS Split, 'PCC VISN 1' AS PilotName, 8 AS OrderID
UNION
SELECT '47' AS Split, 'PCC VISN 22 LONG BEACH' AS PilotName, 9 AS OrderID
UNION
SELECT '49' AS Split, 'PCC VISN 11- Danville' AS PilotName, 11 AS OrderID
UNION
SELECT '66' AS Split, 'PCC VISN 22 LOMA LINDA' AS PilotName, 12 AS OrderID
ORDER BY OrderID
|
|
|
|
sergeant_time
Yak Posting Veteran
68 Posts |
Posted - 10/22/2012 : 11:57:15
|
@ lazerath,
I understand there are many ways to combine the two splits together. I am adding split 3 and 8 together. So instead of selecting CCP and FPCC in the drop list in the report, I just want to select FPCC and it will consolidate both CCP and FPCC together. This is the website I ran into http://www.wiseowl.co.uk/blog/s110/union-query-list-control.htm |
|
|
| |
Topic |
|
|
|
Creating the Union Query for a List Type Control
