Union Select -A doozy

[DougAtAvalon]

Does any one now how to do a union select with a where clause coming from 2 fields.

For example:
Select Field 1... From AAA; Union Select Field 2... From AAA

Now the Where clause is giving me a problem. This is it in plane terms. I want the record to show up if it has "xxx" in field1 or field2 along with some other where criteria
-----------
THIS IS THE ORIGINAL THAT WORKS(please note Loc1 and Loc2 is what changes in the where clauses):

SELECT Providers.ProviderID, IIf(Not IsNull([Company]),[Company],[LastName] & ', ' & [FirstName] & ' ' & [MiddleName]) AS Provider, Providers.CountyLoc1, ProviderServices.ServiceID, Providers.PostalCodeLoc1
FROM Providers INNER JOIN ProviderServices ON Providers.ProviderID = ProviderServices.ProviderID
WHERE (((Providers.CountyLoc1)="Bergen&quot AND ((ProviderServices.ServiceID)=45) AND ((Providers.PostalCodeLoc1)="07834&quot)
ORDER BY IIf(Not IsNull([Company]),[Company],[LastName] & ', ' & [FirstName] & ' ' & [MiddleName]);
------------------------------------------------

THIS IS THE UNION SELECT WITH THE 2 WHERE CLAUSES (please note Loc1 and Loc2 is what changes in the where clauses):

SELECT Providers.ProviderID, IIf(Not IsNull([Company]),[Company],[LastName] & ', ' & [FirstName] & ' ' & [MiddleName]) AS Provider, Providers.CountyLoc1, ProviderServices.ServiceID, Providers.PostalCodeLoc1 FROM Providers INNER JOIN ProviderServices ON Providers.ProviderID = ProviderServices.ProviderID WHERE (((Providers.CountyLoc1)="Bergen&quot AND ((ProviderServices.ServiceID)=45) AND ((Providers.PostalCodeLoc1)="07834&quot); UNION SELECT Providers.ProviderID, IIf(Not IsNull([Company]),[Company],[LastName] & ', ' & [FirstName] & ' ' & [MiddleName]) AS Provider, Providers.CountyLoc1, ProviderServices.ServiceID, Providers.PostalCodeLoc1 FROM Providers INNER JOIN ProviderServices ON Providers.ProviderID = ProviderServices.ProviderID WHERE (((Providers.CountyLoc2)="Bergen&quot AND ((ProviderServices.ServiceID)=45) AND ((Providers.PostalCodeLoc2)="07834&quot)
ORDER BY IIf(Not IsNull([Company]),[Company],[LastName] & ', ' & [FirstName] & ' ' & [MiddleName]);
-----------------------------------------


-Doug

 

[Craig0201]

Doug,

Don't think the cause is the WHERE clause. Think it is in the ORDER BY.
You can only ORDER BY on the first part of your UNION. Delete the ORDER BY on the second part and all should be OK.

Craig

 

[DougAtAvalon]

Thanks for responding. please note there is only 1 ORDER BY clause and Access help says there should only be one and the end of the second select.

any more help or experience?

 

[Sawatzky]

dear dougatavalon,

try this:
SELECT Providers.ProviderID, IIf(Not IsNull([Company]),[Company],[LastName] & ', ' &
[FirstName] & ' ' & [MiddleName]) AS Provider, Providers.CountyLoc1,
ProviderServices.ServiceID, Providers.PostalCodeLoc1 FROM Providers INNER JOIN
ProviderServices ON Providers.ProviderID = ProviderServices.ProviderID WHERE
(((Providers.CountyLoc1)="Bergen&quot AND ((ProviderServices.ServiceID)=45) AND
((Providers.PostalCodeLoc1)="07834&quot);
UNION SELECT Providers.ProviderID, IIf(Not
IsNull([Company]),[Company],[LastName] & ', ' & [FirstName] & ' ' & [MiddleName]) AS
Provider, Providers.CountyLoc1, ProviderServices.ServiceID, Providers.PostalCodeLoc1 FROM
Providers INNER JOIN ProviderServices ON Providers.ProviderID =
ProviderServices.ProviderID WHERE (((Providers.CountyLoc2)="Bergen&quot AND
((ProviderServices.ServiceID)=45) AND ((Providers.PostalCodeLoc2)="07834&quot)
ORDER BY Provider;
HTH
regards astrid

 

[Sawatzky]

dear dougatavalon,
I had a typo in before sorry:

try this:
SELECT Providers.ProviderID, IIf(Not IsNull([Company]),[Company],[LastName] & ', ' &
[FirstName] & ' ' & [MiddleName]) AS Provider, Providers.CountyLoc1,
ProviderServices.ServiceID, Providers.PostalCodeLoc1 FROM Providers
INNER JOIN
ProviderServices ON Providers.ProviderID = ProviderServices.ProviderID
WHERE
(((Providers.CountyLoc1)="Bergen&quot AND ((ProviderServices.ServiceID)=45)
AND
((Providers.PostalCodeLoc1)="07834&quot);
UNION SELECT Providers.ProviderID, IIf(Not
IsNull([Company]),[Company],[LastName] & ', ' & [FirstName] & ' ' &
[MiddleName]) AS
Provider, Providers.CountyLoc2, ProviderServices.ServiceID,
Providers.PostalCodeLoc1 FROM
Providers INNER JOIN ProviderServices ON Providers.ProviderID =
ProviderServices.ProviderID WHERE (((Providers.CountyLoc2)="Bergen&quot
AND
((ProviderServices.ServiceID)=45) AND
((Providers.PostalCodeLoc2)="07834&quot)
ORDER BY Provider;

regards Astrid

 

[MichaelRed]

I more-or-less agree with Sawatzky, the use of a conditional in the orderby clause is -at best- weird. Another "problem" is the trmination character in the midst of the query (";" JUST prior to the "Union Select", which doesn't show up in any of my sample union queries.

Aside from this, It is curious to see the Union query used in what appears to be a convoluted self join process, where the only difference is in the select criteria - and for only a single field. Is there something I'm missing? Couldn't this be more easily accomplished with additional criteria on the single select?

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[DougAtAvalon]

i came up with a solution. it uses 2 queries then a Union select on the third. The problem was i had to change the caption because it combined data from 2 fields. Hope this helps someone in the future