Multi-Union query Order by Sections?

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

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??

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...

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.

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:

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???

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

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;

Perfect!! I love it when the actual SQL is written out (copy and paste helps so much!!)