Solved

Multi-Union query Order by Sections?

Posted on 2013-02-05
10
384 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

760 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

20 Experts available now in Live!

Get 1:1 Help Now