Criteria help

  • Thread starter Thread starter RKH
  • Start date Start date
R

RKH

The following sql query returns a good result except some of the enteries do
have a business address and I will be using the query to produce labels.
What I would like to do is if the BusinessAddress1, BusinessCity,
BusinessState and BusinessPostalCode are blank then use the Home Address,
HomeCity, HomeState and HomePostalCode. So will I be able to do this using
the criteria or do I have to write a sql query. Either way would someone
give some help to acheive a good result?

Thanks,

Richard

SELECT [Main Directory].[Sr Pastor], [Main Directory].NoPastorChurch, [Main
Directory].[College Personnel], [Main Directory].[Ast Pastor], [Main
Directory].Other, [Main Directory].Title, [Main Directory].FirstName, [Main
Directory].LastName, [Main Directory].Business, [Main
Directory].BusinessAddress1, [Main Directory].BusinessCity, [Main
Directory].BusinessState, [Main Directory].BusinessPostalCode, [Main
Directory].HomeAddress, [Main Directory].HomeCity, [Main
Directory].HomeState, [Main Directory].HomePostalCode
FROM [Main Directory]
WHERE ((([Main Directory].[Sr Pastor])="Y")) OR ((([Main Directory].[Ast
Pastor])="Y")) OR ((([Main Directory].[College Personnel])="Y")) OR ((([Main
Directory].Other)="Y")) OR ((([Main Directory].NoPastorChurch)="Y"));
 
SELECT [Main Directory].[Sr Pastor],
[Main Directory].NoPastorChurch,
[Main Directory].[College Personnel],
[Main Directory].[Ast Pastor],
[Main Directory].Other,
[Main Directory].Title,
[Main Directory].FirstName,
[Main Directory].LastName,
[Main Directory].Business,
[Main Directory].BusinessAddress1,
[Main Directory].BusinessCity,
[Main Directory].BusinessState,
[Main Directory].BusinessPostalCode
FROM [Main Directory]
WHERE ([Main Directory].[Sr Pastor]="Y"
OR [Main Directory].[Ast Pastor]="Y"
OR [Main Directory].[College Personnel]="Y"
OR [Main Directory].Other="Y"
OR [Main Directory].NoPastorChurch="Y")
AND [Main Directory].BusinessCity Is Not Null
UNION ALL
SELECT [Main Directory].[Sr Pastor],
[Main Directory].NoPastorChurch,
[Main Directory].[College Personnel],
[Main Directory].[Ast Pastor],
[Main Directory].Other,
[Main Directory].Title,
[Main Directory].FirstName,
[Main Directory].LastName,
[Main Directory].Business,
[Main Directory].HomeAddress,
[Main Directory].HomeCity,
[Main Directory].HomeState,
[Main Directory].HomePostalCode
FROM [Main Directory]
WHERE ([Main Directory].[Sr Pastor]="Y"
OR [Main Directory].[Ast Pastor]="Y"
OR [Main Directory].[College Personnel]="Y"
OR [Main Directory].Other="Y"
OR [Main Directory].NoPastorChurch="Y")
AND [Main Directory].BusinessCity Is Null ;

Hopefully I didn't make a mess of the OR/AND statement logic.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


RKH said:
The following sql query returns a good result except some of the enteries do
have a business address and I will be using the query to produce labels.
What I would like to do is if the BusinessAddress1, BusinessCity,
BusinessState and BusinessPostalCode are blank then use the Home Address,
HomeCity, HomeState and HomePostalCode. So will I be able to do this using
the criteria or do I have to write a sql query. Either way would someone
give some help to acheive a good result?

Thanks,

Richard

SELECT [Main Directory].[Sr Pastor], [Main Directory].NoPastorChurch, [Main
Directory].[College Personnel], [Main Directory].[Ast Pastor], [Main
Directory].Other, [Main Directory].Title, [Main Directory].FirstName, [Main
Directory].LastName, [Main Directory].Business, [Main
Directory].BusinessAddress1, [Main Directory].BusinessCity, [Main
Directory].BusinessState, [Main Directory].BusinessPostalCode, [Main
Directory].HomeAddress, [Main Directory].HomeCity, [Main
Directory].HomeState, [Main Directory].HomePostalCode
FROM [Main Directory]
WHERE ((([Main Directory].[Sr Pastor])="Y")) OR ((([Main Directory].[Ast
Pastor])="Y")) OR ((([Main Directory].[College Personnel])="Y")) OR ((([Main
Directory].Other)="Y")) OR ((([Main Directory].NoPastorChurch)="Y"));
Click to expand...
 
Thank you very much for the help. I never would of thought of Unions. I have
not used unions but need to read more about them. Do you have a link where I
can read and understand them?

Thanks,

Richard

Jerry Whittle said:
SELECT [Main Directory].[Sr Pastor],
[Main Directory].NoPastorChurch,
[Main Directory].[College Personnel],
[Main Directory].[Ast Pastor],
[Main Directory].Other,
[Main Directory].Title,
[Main Directory].FirstName,
[Main Directory].LastName,
[Main Directory].Business,
[Main Directory].BusinessAddress1,
[Main Directory].BusinessCity,
[Main Directory].BusinessState,
[Main Directory].BusinessPostalCode
FROM [Main Directory]
WHERE ([Main Directory].[Sr Pastor]="Y"
OR [Main Directory].[Ast Pastor]="Y"
OR [Main Directory].[College Personnel]="Y"
OR [Main Directory].Other="Y"
OR [Main Directory].NoPastorChurch="Y")
AND [Main Directory].BusinessCity Is Not Null
UNION ALL
SELECT [Main Directory].[Sr Pastor],
[Main Directory].NoPastorChurch,
[Main Directory].[College Personnel],
[Main Directory].[Ast Pastor],
[Main Directory].Other,
[Main Directory].Title,
[Main Directory].FirstName,
[Main Directory].LastName,
[Main Directory].Business,
[Main Directory].HomeAddress,
[Main Directory].HomeCity,
[Main Directory].HomeState,
[Main Directory].HomePostalCode
FROM [Main Directory]
WHERE ([Main Directory].[Sr Pastor]="Y"
OR [Main Directory].[Ast Pastor]="Y"
OR [Main Directory].[College Personnel]="Y"
OR [Main Directory].Other="Y"
OR [Main Directory].NoPastorChurch="Y")
AND [Main Directory].BusinessCity Is Null ;

Hopefully I didn't make a mess of the OR/AND statement logic.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


RKH said:
The following sql query returns a good result except some of the enteries
do
have a business address and I will be using the query to produce labels.
What I would like to do is if the BusinessAddress1, BusinessCity,
BusinessState and BusinessPostalCode are blank then use the Home Address,
HomeCity, HomeState and HomePostalCode. So will I be able to do this
using
the criteria or do I have to write a sql query. Either way would someone
give some help to acheive a good result?

Thanks,

Richard

SELECT [Main Directory].[Sr Pastor], [Main Directory].NoPastorChurch,
[Main
Directory].[College Personnel], [Main Directory].[Ast Pastor], [Main
Directory].Other, [Main Directory].Title, [Main Directory].FirstName,
[Main
Directory].LastName, [Main Directory].Business, [Main
Directory].BusinessAddress1, [Main Directory].BusinessCity, [Main
Directory].BusinessState, [Main Directory].BusinessPostalCode, [Main
Directory].HomeAddress, [Main Directory].HomeCity, [Main
Directory].HomeState, [Main Directory].HomePostalCode
FROM [Main Directory]
WHERE ((([Main Directory].[Sr Pastor])="Y")) OR ((([Main Directory].[Ast
Pastor])="Y")) OR ((([Main Directory].[College Personnel])="Y")) OR
((([Main
Directory].Other)="Y")) OR ((([Main Directory].NoPastorChurch)="Y"));
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help! I can't understand why this won't work! 3
HELP! AND / OR criteria 4
hope this is an easy one for somebody 5
Help with custom search form - should I use rec 1
one simple criteria input box - google style 2
Form Query and GridView for ASP.NET 2.0 1
NEED HELP with a multi-criteria query 2
Multiple search criteria 1

Back
Top