Solved

Multi-Union query Order by Sections?

Posted on 2013-02-05
10
386 Views
Last Modified: 2013-02-06
Hello,

So I have a Union query with 7 different queries. It is currently working great except for one small detail which is when it orders all of the results, it doesn't sectionalize them as how the SQL code is sectioned.......Here is the SQL code:

PARAMETERS [Enter Counties sep by commas, Blank=ALL] Text ( 255 );
SELECT rvs_prov_company.full_name AS Provider, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM (rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT DISTINCT rvs_prov_company.full_name AS Provider, "see above" AS Street, "see above" AS Street2, "see above" AS City, "see above" AS State, "see above" AS ZipCode, "see above" AS County, "see above" AS Phone, "see above" AS Fax
FROM (rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT DISTINCT vend_master_v.vendornm AS Vendor, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM rvs_prov_company INNER JOIN ((rv_prov_vendinfo INNER JOIN vend_master_v ON rv_prov_vendinfo.vendor_id = vend_master_v.vendor_id) INNER JOIN zipcode_lu ON vend_master_v.zip5 = zipcode_lu.zipcode) ON rvs_prov_company.prov_keyid = rv_prov_vendinfo.prov_keyid
WHERE (((rv_prov_vendinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT rvs_prov_company.full_name AS Provider, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((rv_prov_addinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT DISTINCT rvs_prov_company.full_name AS Provider, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((rv_prov_addinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT rvs_prov_company.full_name AS Provider, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM","OBG","OB")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION SELECT DISTINCT rvs_prov_company.full_name AS Provider, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((zipcode_lu.preferred)="P") AND ((rv_prov_addinfo.sequence)=1) AND ((rvs_prov_company.term_date) Is Null) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM","OBG","OB")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null));


So you see I need them to be ordered into their respective sections instead of finding for example, ABC MED GRP in 4 out of the 7 queries and ordering them one after another in the result set.

Can someone help me with how to order them please?
0
Comment
Question by:IEHP1
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 38857604
You could add another field '"1" as orderField', '"2" as orderField' etc  to each section
and order by that.
0
 

Author Comment

by:IEHP1
ID: 38857629
Yes like this:

PARAMETERS [Enter Counties sep by commas, Blank=ALL] Text ( 255 );
SELECT Category, Entity, Street, Street2, City, State, Zip5, County, Phone, Fax
FROM (
SELECT "Active Provider Sites" AS Category, rvs_prov_company.full_name AS Entity, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM (rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT DISTINCT "Active Distinct Providers" AS Category, rvs_prov_company.full_name AS Entity, "see above" AS Street, "see above" AS Street2, "see above" AS City, "see above" AS State, "see above" AS ZipCode, "see above" AS County, "see above" AS Phone, "see above" AS Fax
FROM (rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT DISTINCT "Active Distinct Vendors" AS Category, vend_master_v.vendornm AS Entity, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM rvs_prov_company INNER JOIN ((rv_prov_vendinfo INNER JOIN vend_master_v ON rv_prov_vendinfo.vendor_id = vend_master_v.vendor_id) INNER JOIN zipcode_lu ON vend_master_v.zip5 = zipcode_lu.zipcode) ON rvs_prov_company.prov_keyid = rv_prov_vendinfo.prov_keyid
WHERE (((rv_prov_vendinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT "Active PCP Sites-No OB" AS Category, rvs_prov_company.full_name AS Entity, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((rv_prov_addinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT DISTINCT "Active Distinct PCPs-No OB" AS Entity, rvs_prov_company.full_name AS Provider, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((rv_prov_addinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT "Active PCP Sites" AS Category, rvs_prov_company.full_name AS Entity, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM","OBG","OB")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT DISTINCT "Active Distinct PCPs" AS Category, rvs_prov_company.full_name AS Entity, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((zipcode_lu.preferred)="P") AND ((rv_prov_addinfo.sequence)=1) AND ((rvs_prov_company.term_date) Is Null) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM","OBG","OB")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null));


They don't order themselves as I need them to be seen and I don't want to have to put 1, 2, 3, etc. in front of each one just to order it the way I need it. Is there any way to avoid having to put 1, 2, 3, etc. in front of each one??
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38857646
id you don't specify an order by clause on a select statement then the order in which the result set is return is RANDOM (well down to what the database engine determines is the most efficient manner in which to retrieve the data at that time...)

SQL is not processed in any particular top down manner (apart from the case statement where when expression ordering is important....)

so if you want rows return grouped by and in the order of each unioned select...
then you must ensure that each select has an Indicator value on ... (as jerryb30 has suggested) this however could mean that rows you currently lose as duplicates (you're using UNION not UNION all so an implicit DISTINCT is being applied presently) will no longer be removed so your query may need further modifications to produce the result you desire...

again since you are using UNION there is no point in have  the DISTINCT statement on some of the individual selects as deduplication is going to occur anyway...
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38857659
If you want to be able to separate out distinct records, you'll have to add some field to retain source ofeach record.
Try UNION ALL between sections, and see if that  helps.
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 38857686
try this


PARAMETERS [Enter Counties sep by commas, Blank=ALL] Text ( 255 );
SELECT A.Category, A.Entity, A.Street, A.Street2, A.City, A.State, A.Zip5, A.County, A.Phone, A.Fax
FROM (
SELECT "Active Provider Sites" AS Category, rvs_prov_company.full_name AS Entity, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM (rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT DISTINCT "Active Distinct Providers" AS Category, rvs_prov_company.full_name AS Entity, "see above" AS Street, "see above" AS Street2, "see above" AS City, "see above" AS State, "see above" AS ZipCode, "see above" AS County, "see above" AS Phone, "see above" AS Fax
FROM (rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT DISTINCT "Active Distinct Vendors" AS Category, vend_master_v.vendornm AS Entity, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM rvs_prov_company INNER JOIN ((rv_prov_vendinfo INNER JOIN vend_master_v ON rv_prov_vendinfo.vendor_id = vend_master_v.vendor_id) INNER JOIN zipcode_lu ON vend_master_v.zip5 = zipcode_lu.zipcode) ON rvs_prov_company.prov_keyid = rv_prov_vendinfo.prov_keyid
WHERE (((rv_prov_vendinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT "Active PCP Sites-No OB" AS Category, rvs_prov_company.full_name AS Entity, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((rv_prov_addinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT DISTINCT "Active Distinct PCPs-No OB" AS Entity, rvs_prov_company.full_name AS Provider, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((rv_prov_addinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT "Active PCP Sites" AS Category, rvs_prov_company.full_name AS Entity, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM","OBG","OB")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT DISTINCT "Active Distinct PCPs" AS Category, rvs_prov_company.full_name AS Entity, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((zipcode_lu.preferred)="P") AND ((rv_prov_addinfo.sequence)=1) AND ((rvs_prov_company.term_date) Is Null) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM","OBG","OB")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))) As A
Order By A.Category
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38859012
My system doesn't like union queries, One way around this is to add the Section to each part of your query, then make that into a subquery and drop the Section from the results of that, similar to:union query
0
 

Author Comment

by:IEHP1
ID: 38860371
Sorry for the late response, but I have been tinkering with the query and have it the way it should be except for one important part, which is that it now has

1. OrderID as the first column (in the order of 1 through 7 sequentially sectionalized as it should be)
2. Category as the second column (in the order sequentially sectionalized as it should be)
3. Entity as the third column (some sections are ordered sequentially while others aren't???) (this is the Provider's [full_name]).  (Sidenote:  not sure why it gave me a 'Duplicate output Alias name' error for "Entity" because the Original version I have still didn't give me any error about that when I had it set up the same??)

I can't figure out why some sections order the [full_name] in Ascending order while others don't??

Anyway here is my SQL for this problem:

PARAMETERS [Enter Counties sep by commas, Blank=ALL] Text ( 255 );
SELECT "1" AS OrderID, "Active Provider Sites" AS Category, rvs_prov_company.full_name AS Entity, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM (rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode
WHERE (((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
ORDER BY rvs_prov_company.full_name
UNION ALL
SELECT DISTINCT "2" AS OrderID, "Active Distinct Providers" AS Category, rvs_prov_company.full_name AS Entity2, "see above" AS Street, "see above" AS Street2, "see above" AS City, "see above" AS State, "see above" AS ZipCode, "see above" AS County, "see above" AS Phone, "see above" AS Fax
FROM (rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode
WHERE (((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION ALL
SELECT DISTINCT "3" AS OrderID, "Active Distinct Vendors" AS Category, vend_master_v.vendornm AS Entity3, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM rvs_prov_company INNER JOIN ((rv_prov_vendinfo INNER JOIN vend_master_v ON rv_prov_vendinfo.vendor_id = vend_master_v.vendor_id) INNER JOIN zipcode_lu ON vend_master_v.zip5 = zipcode_lu.zipcode) ON rvs_prov_company.prov_keyid = rv_prov_vendinfo.prov_keyid
WHERE (((rv_prov_vendinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION ALL
SELECT "4" AS OrderID, "Active PCP Sites-No OB" AS Category, rvs_prov_company.full_name AS Entity4, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION ALL
SELECT DISTINCT "5" AS OrderID, "Active Distinct PCPs-No OB" AS Category, rvs_prov_company.full_name AS Entity5, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((rv_prov_addinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION ALL
SELECT "6" AS OrderID, "Active PCP Sites" AS Category, rvs_prov_company.full_name AS Entity6, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM","OBG","OB")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION ALL
SELECT DISTINCT "7" AS OrderID, "Active Distinct PCPs" AS Category, rvs_prov_company.full_name AS Entity7, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((zipcode_lu.preferred)="P") AND ((rv_prov_addinfo.sequence)=1) AND ((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM","OBG","OB")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null));


Notice I have tried to insert an ORDER BY clause to get the first section to order it correctly, but that doesn't work???
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38860462
you only have 1 order by clause and that always comes at the end of the whole statement

your order by clause

order by orderid,category,entity,fullname
0
 

Author Comment

by:IEHP1
ID: 38860469
So hey, I have been working with it and here is the Winner: (capricorn 1 hit the nail on the head here -- details about the number of parentheses helped-like 3 parentheses just before the "AS A")

PARAMETERS [Enter Counties sep by commas, Blank=ALL] Text ( 255 );
SELECT ['Provider Network Snapshot'].OrderID, ['Provider Network Snapshot'].Category, ['Provider Network Snapshot'].Entity, ['Provider Network Snapshot'].street, ['Provider Network Snapshot'].street2, ['Provider Network Snapshot'].city, ['Provider Network Snapshot'].state, ['Provider Network Snapshot'].zip5, ['Provider Network Snapshot'].county, ['Provider Network Snapshot'].phone, ['Provider Network Snapshot'].fax
FROM (SELECT "1" AS OrderID, "Active Provider Sites" AS Category, rvs_prov_company.full_name AS Entity, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM (rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode
WHERE (((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION ALL
SELECT DISTINCT "2" AS OrderID, "Active Distinct Providers" AS Category, rvs_prov_company.full_name AS Entity2, "see OrderID 1" AS Street, "see OrderID 1" AS Street2, "see OrderID 1" AS City, "see OrderID 1" AS State, "see OrderID 1" AS ZipCode, "see OrderID 1" AS County, "see OrderID 1" AS Phone, "see OrderID 1" AS Fax
FROM (rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode
WHERE (((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION ALL
SELECT DISTINCT "3" AS OrderID, "Active Distinct Vendors" AS Category, vend_master_v.vendornm AS Entity3, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM rvs_prov_company INNER JOIN ((rv_prov_vendinfo INNER JOIN vend_master_v ON rv_prov_vendinfo.vendor_id = vend_master_v.vendor_id) INNER JOIN zipcode_lu ON vend_master_v.zip5 = zipcode_lu.zipcode) ON rvs_prov_company.prov_keyid = rv_prov_vendinfo.prov_keyid
WHERE (((rv_prov_vendinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION ALL
SELECT "4" AS OrderID, "Active PCP Sites-No OB" AS Category, rvs_prov_company.full_name AS Entity4, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION ALL
SELECT DISTINCT "5" AS OrderID, "Active Distinct PCPs-No OB" AS Category, rvs_prov_company.full_name AS Entity5, "see OrderID 4" AS Street, "see OrderID 4" AS Street2, "see OrderID 4" AS City, "see OrderID 4" AS State, "see OrderID 4" AS ZipCode, "see OrderID 4" AS County, "see OrderID 4" AS Phone, "see OrderID 4" AS Fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((rv_prov_addinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION ALL
SELECT "6" AS OrderID, "Active PCP Sites" AS Category, rvs_prov_company.full_name AS Entity6, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM","OBG","OB")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION ALL
SELECT DISTINCT "7" AS OrderID, "Active Distinct PCPs" AS Category, rvs_prov_company.full_name AS Entity7, "see OrderID 6" AS Street, "see OrderID 6" AS Street2, "see OrderID 6" AS City, "see OrderID 6" AS State, "see OrderID 6" AS ZipCode, "see OrderID 6" AS County, "see OrderID 6" AS Phone, "see OrderID 6" AS Fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((zipcode_lu.preferred)="P") AND ((rv_prov_addinfo.sequence)=1) AND ((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM","OBG","OB")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null)))  AS ['Provider Network Snapshot']
ORDER BY ['Provider Network Snapshot'].OrderID, ['Provider Network Snapshot'].Category, ['Provider Network Snapshot'].Entity;
0
 

Author Closing Comment

by:IEHP1
ID: 38860473
Perfect!! I love it when the actual SQL is written out (copy and paste helps so much!!)
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now