Multiple criteria

[JeffH]

I have a query on which I would like to select different employees based on
criteria from two different fields. For example, I would like to have each
employee over age 55 selected or every employee that works at a particular
location. This is just an example, but it gets at what I want to do.

This has got to be easier than I think. Thanks.

 

[Ken Sheridan]

Its really just a question of getting your teeth around the logic. The trick
is to test each for OR <parameter> IS NULL, so using your example a query
might be along these lines:

SELECT *
FROM Employees
WHERE (GetAge(DoB) >= [Enter age:]
OR [Enter age:] IS NULL)
AND (Location = [Enter location:]
OR [Enter location:] IS NULL);

I've assumed for this example that an employee's age is returned by a GetAge
function which takes their date of birth, a column in the table, as its
argument and computes their current age. But that's secondary to the main
point under discussion here.

With each parenthesised expression, if a value is entered at the parameter
prompt then it will evaluate to True for those rows which meet the criterion.
If the value is omitted and the parameter is therefore Null then it will
evaluate to True for every row by virtue of the Boolean OR operation, which
requires either expression to be True.

So if a user enters an age bur not a location then the first part of the
Boolean AND those rows where the employee's age is equal to or more than the
value entered will be returned. The second part of the AND operation will
evaluate to True for all rows as the parameter is Null. The Boolean AND
operation requires both parts to be True, so only those rows where the age is
equal to or more than the value entered will be returned.

A user could of course enter values for both parameters in which case rows
for employee's at the location in question whose age is equal to or more than
the value entered will be returned.

I hope the above illustrates the principles involved and allows you to apply
them to your real world requirements, but post back if you have any problems
doing so. You are not limited to two parameters of course; you can tack as
many parenthesised OR expressions together with ANDs as necessary, which
'optionalizes' all of them allowing a user to enter values for as few or as
many as they wish.

Ken Sheridan
Stafford, England

 

[JeffH]

It looks that I have to access the code to accomplishes your solution. I
generally have not done this. I am "ok" at writing queries, but limited with
visual basic. Right now I'm working on my solution by using the "IIF"
function and building my selection criteria this way. It does get to be a
very complicated formula and entering the code appears quicker (your
solution), but how do I "rewrite" the queries code?

Its really just a question of getting your teeth around the logic. The trick
is to test each for OR <parameter> IS NULL, so using your example a query
might be along these lines:

SELECT *
FROM Employees
WHERE (GetAge(DoB) >= [Enter age:]
OR [Enter age:] IS NULL)
AND (Location = [Enter location:]
OR [Enter location:] IS NULL);

I've assumed for this example that an employee's age is returned by a GetAge
function which takes their date of birth, a column in the table, as its
argument and computes their current age. But that's secondary to the main
point under discussion here.

With each parenthesised expression, if a value is entered at the parameter
prompt then it will evaluate to True for those rows which meet the criterion.
If the value is omitted and the parameter is therefore Null then it will
evaluate to True for every row by virtue of the Boolean OR operation, which
requires either expression to be True.

So if a user enters an age bur not a location then the first part of the
Boolean AND those rows where the employee's age is equal to or more than the
value entered will be returned. The second part of the AND operation will
evaluate to True for all rows as the parameter is Null. The Boolean AND
operation requires both parts to be True, so only those rows where the age is
equal to or more than the value entered will be returned.

A user could of course enter values for both parameters in which case rows
for employee's at the location in question whose age is equal to or more than
the value entered will be returned.

I hope the above illustrates the principles involved and allows you to apply
them to your real world requirements, but post back if you have any problems
doing so. You are not limited to two parameters of course; you can tack as
many parenthesised OR expressions together with ANDs as necessary, which
'optionalizes' all of them allowing a user to enter values for as few or as
many as they wish.

Ken Sheridan
Stafford, England

I have a query on which I would like to select different employees based on
criteria from two different fields. For example, I would like to have each
employee over age 55 selected or every employee that works at a particular
location. This is just an example, but it gets at what I want to do.

This has got to be easier than I think. Thanks.

 

[Ken Sheridan]

All you have to do is switch from query design view to SQL view. It is
possible to enter the criteria in design view, e.g. with my example for the
age column you'd put the following on the first 'criteria' line:

= [Enter age:] OR [Enter age:] IS NULL

and the following on the first 'criteria' line of the location column:

[Enter location:] OR [Enter location:] IS NULL

However, you'll find that if you save the query and reopen it in design view
Access will have moved things around. The underlying logic will be the same,
and it will still work, but it will be harder to 'read' the query's design
and to add any additional criteria than in SQL view. If you do write it in
SQL view be sure to save it in SQL view too. If you switch to design view
and save it Access will move the furniture around again. Access also throws
in a huge number of unnecessary parentheses when you use query design view,
and these can make it difficult to spot the ones which matter if you switch
to SQL view to make amendments. Writing it from scratch as SQL means you
only put in the logically necessary parentheses. By all means start with
design view for adding the necessary tables and columns to the query, but
with a WHERE clause of this nature switching to SQL view to enter the
criteria will be a lot easier.

When writing SQL you can break the lines wherever you wish for readability,
and you can indent lines to make things even clearer. You might well find
that next time you open it in design view the line breaks and indents won't
be exactly as you put them in, though. Access can try to hard to be helpful
sometimes! The SQL is just text of course, so when building a complex query
one thing you can do as you go along is to copy and paste the SQL at various
stages into Word or Notepad, caption each SQL statement with explanatory
notes and save the text file. That way you can always go back to the text,
copy the SQL and paste it back into the SQL window in Access if something
goes wrong and you need to revert to an earlier stage in the design of a
complex query.

Ken Sheridan
Stafford, England

It looks that I have to access the code to accomplishes your solution. I
generally have not done this. I am "ok" at writing queries, but limited with
visual basic. Right now I'm working on my solution by using the "IIF"
function and building my selection criteria this way. It does get to be a
very complicated formula and entering the code appears quicker (your
solution), but how do I "rewrite" the queries code?

Its really just a question of getting your teeth around the logic. The trick
is to test each for OR <parameter> IS NULL, so using your example a query
might be along these lines:

SELECT *
FROM Employees
WHERE (GetAge(DoB) >= [Enter age:]
OR [Enter age:] IS NULL)
AND (Location = [Enter location:]
OR [Enter location:] IS NULL);

I've assumed for this example that an employee's age is returned by a GetAge
function which takes their date of birth, a column in the table, as its
argument and computes their current age. But that's secondary to the main
point under discussion here.

With each parenthesised expression, if a value is entered at the parameter
prompt then it will evaluate to True for those rows which meet the criterion.
If the value is omitted and the parameter is therefore Null then it will
evaluate to True for every row by virtue of the Boolean OR operation, which
requires either expression to be True.

So if a user enters an age bur not a location then the first part of the
Boolean AND those rows where the employee's age is equal to or more than the
value entered will be returned. The second part of the AND operation will
evaluate to True for all rows as the parameter is Null. The Boolean AND
operation requires both parts to be True, so only those rows where the age is
equal to or more than the value entered will be returned.

A user could of course enter values for both parameters in which case rows
for employee's at the location in question whose age is equal to or more than
the value entered will be returned.

I hope the above illustrates the principles involved and allows you to apply
them to your real world requirements, but post back if you have any problems
doing so. You are not limited to two parameters of course; you can tack as
many parenthesised OR expressions together with ANDs as necessary, which
'optionalizes' all of them allowing a user to enter values for as few or as
many as they wish.

Ken Sheridan
Stafford, England

I have a query on which I would like to select different employees based on
criteria from two different fields. For example, I would like to have each
employee over age 55 selected or every employee that works at a particular
location. This is just an example, but it gets at what I want to do.

This has got to be easier than I think. Thanks.

 

[JeffH]

Thanks I did figure out how to open the query in SQL vlew and based upon your
example modified the code. It was much easier. Now I have to follow the
rest of your recomemndation.

Thanks again.

I think I need to learn more SQL code.

All you have to do is switch from query design view to SQL view. It is
possible to enter the criteria in design view, e.g. with my example for the
age column you'd put the following on the first 'criteria' line:

= [Enter age:] OR [Enter age:] IS NULL

and the following on the first 'criteria' line of the location column:

[Enter location:] OR [Enter location:] IS NULL

However, you'll find that if you save the query and reopen it in design view
Access will have moved things around. The underlying logic will be the same,
and it will still work, but it will be harder to 'read' the query's design
and to add any additional criteria than in SQL view. If you do write it in
SQL view be sure to save it in SQL view too. If you switch to design view
and save it Access will move the furniture around again. Access also throws
in a huge number of unnecessary parentheses when you use query design view,
and these can make it difficult to spot the ones which matter if you switch
to SQL view to make amendments. Writing it from scratch as SQL means you
only put in the logically necessary parentheses. By all means start with
design view for adding the necessary tables and columns to the query, but
with a WHERE clause of this nature switching to SQL view to enter the
criteria will be a lot easier.

When writing SQL you can break the lines wherever you wish for readability,
and you can indent lines to make things even clearer. You might well find
that next time you open it in design view the line breaks and indents won't
be exactly as you put them in, though. Access can try to hard to be helpful
sometimes! The SQL is just text of course, so when building a complex query
one thing you can do as you go along is to copy and paste the SQL at various
stages into Word or Notepad, caption each SQL statement with explanatory
notes and save the text file. That way you can always go back to the text,
copy the SQL and paste it back into the SQL window in Access if something
goes wrong and you need to revert to an earlier stage in the design of a
complex query.

Ken Sheridan
Stafford, England

It looks that I have to access the code to accomplishes your solution. I
generally have not done this. I am "ok" at writing queries, but limited with
visual basic. Right now I'm working on my solution by using the "IIF"
function and building my selection criteria this way. It does get to be a
very complicated formula and entering the code appears quicker (your
solution), but how do I "rewrite" the queries code?

Its really just a question of getting your teeth around the logic. The trick
is to test each for OR <parameter> IS NULL, so using your example a query
might be along these lines:

SELECT *
FROM Employees
WHERE (GetAge(DoB) >= [Enter age:]
OR [Enter age:] IS NULL)
AND (Location = [Enter location:]
OR [Enter location:] IS NULL);

I've assumed for this example that an employee's age is returned by a GetAge
function which takes their date of birth, a column in the table, as its
argument and computes their current age. But that's secondary to the main
point under discussion here.

With each parenthesised expression, if a value is entered at the parameter
prompt then it will evaluate to True for those rows which meet the criterion.
If the value is omitted and the parameter is therefore Null then it will
evaluate to True for every row by virtue of the Boolean OR operation, which
requires either expression to be True.

So if a user enters an age bur not a location then the first part of the
Boolean AND those rows where the employee's age is equal to or more than the
value entered will be returned. The second part of the AND operation will
evaluate to True for all rows as the parameter is Null. The Boolean AND
operation requires both parts to be True, so only those rows where the age is
equal to or more than the value entered will be returned.

A user could of course enter values for both parameters in which case rows
for employee's at the location in question whose age is equal to or more than
the value entered will be returned.

I hope the above illustrates the principles involved and allows you to apply
them to your real world requirements, but post back if you have any problems
doing so. You are not limited to two parameters of course; you can tack as
many parenthesised OR expressions together with ANDs as necessary, which
'optionalizes' all of them allowing a user to enter values for as few or as
many as they wish.

Ken Sheridan
Stafford, England

:

I have a query on which I would like to select different employees based on
criteria from two different fields. For example, I would like to have each
employee over age 55 selected or every employee that works at a particular
location. This is just an example, but it gets at what I want to do.

This has got to be easier than I think. Thanks.

 

[Ken Sheridan]

If you want a good general primer on SQL the following is well regarded,
though I've not seen it myself:


http://www.amazon.com/SQL-Queries-M...=sr_1_1?ie=UTF8&s=books&qid=1232467450&sr=1-1


For more advanced stuff, once you have a good grasp of the basics I can
personally recommend:


http://www.amazon.com/Joe-Celkos-SQ...=sr_1_1?ie=UTF8&s=books&qid=1232467710&sr=1-1


Ken Sheridan
Stafford, England

Thanks I did figure out how to open the query in SQL vlew and based upon your
example modified the code. It was much easier. Now I have to follow the
rest of your recomemndation.

Thanks again.

I think I need to learn more SQL code.

All you have to do is switch from query design view to SQL view. It is
possible to enter the criteria in design view, e.g. with my example for the
age column you'd put the following on the first 'criteria' line:

= [Enter age:] OR [Enter age:] IS NULL

and the following on the first 'criteria' line of the location column:

[Enter location:] OR [Enter location:] IS NULL

However, you'll find that if you save the query and reopen it in design view
Access will have moved things around. The underlying logic will be the same,
and it will still work, but it will be harder to 'read' the query's design
and to add any additional criteria than in SQL view. If you do write it in
SQL view be sure to save it in SQL view too. If you switch to design view
and save it Access will move the furniture around again. Access also throws
in a huge number of unnecessary parentheses when you use query design view,
and these can make it difficult to spot the ones which matter if you switch
to SQL view to make amendments. Writing it from scratch as SQL means you
only put in the logically necessary parentheses. By all means start with
design view for adding the necessary tables and columns to the query, but
with a WHERE clause of this nature switching to SQL view to enter the
criteria will be a lot easier.

When writing SQL you can break the lines wherever you wish for readability,
and you can indent lines to make things even clearer. You might well find
that next time you open it in design view the line breaks and indents won't
be exactly as you put them in, though. Access can try to hard to be helpful
sometimes! The SQL is just text of course, so when building a complex query
one thing you can do as you go along is to copy and paste the SQL at various
stages into Word or Notepad, caption each SQL statement with explanatory
notes and save the text file. That way you can always go back to the text,
copy the SQL and paste it back into the SQL window in Access if something
goes wrong and you need to revert to an earlier stage in the design of a
complex query.

Ken Sheridan
Stafford, England

It looks that I have to access the code to accomplishes your solution. I
generally have not done this. I am "ok" at writing queries, but limited with
visual basic. Right now I'm working on my solution by using the "IIF"
function and building my selection criteria this way. It does get to be a
very complicated formula and entering the code appears quicker (your
solution), but how do I "rewrite" the queries code?



:

Its really just a question of getting your teeth around the logic. The trick
is to test each for OR <parameter> IS NULL, so using your example a query
might be along these lines:

SELECT *
FROM Employees
WHERE (GetAge(DoB) >= [Enter age:]
OR [Enter age:] IS NULL)
AND (Location = [Enter location:]
OR [Enter location:] IS NULL);

I've assumed for this example that an employee's age is returned by a GetAge
function which takes their date of birth, a column in the table, as its
argument and computes their current age. But that's secondary to the main
point under discussion here.

With each parenthesised expression, if a value is entered at the parameter
prompt then it will evaluate to True for those rows which meet the criterion.
If the value is omitted and the parameter is therefore Null then it will
evaluate to True for every row by virtue of the Boolean OR operation, which
requires either expression to be True.

So if a user enters an age bur not a location then the first part of the
Boolean AND those rows where the employee's age is equal to or more than the
value entered will be returned. The second part of the AND operation will
evaluate to True for all rows as the parameter is Null. The Boolean AND
operation requires both parts to be True, so only those rows where the age is
equal to or more than the value entered will be returned.

A user could of course enter values for both parameters in which case rows
for employee's at the location in question whose age is equal to or more than
the value entered will be returned.

I hope the above illustrates the principles involved and allows you to apply
them to your real world requirements, but post back if you have any problems
doing so. You are not limited to two parameters of course; you can tack as
many parenthesised OR expressions together with ANDs as necessary, which
'optionalizes' all of them allowing a user to enter values for as few or as
many as they wish.

Ken Sheridan
Stafford, England

:

I have a query on which I would like to select different employees based on
criteria from two different fields. For example, I would like to have each
employee over age 55 selected or every employee that works at a particular
location. This is just an example, but it gets at what I want to do.

This has got to be easier than I think. Thanks.