UNION, WHERE or Something else?

I have a mailing list where there are multiple yes/no fields. A record can
have multiple Yes or just one. One of the fields is a Text for emails. Not
all records have an email.

DB=Maillist
Fname-Text
Lname-Text
Email-Text
SA-Yes/No
PA-Yes/No
RC-Yes/No
CA-Yes/No

I am trying to create a query to find the emails address of records with a
yes in one or more of the field(s). The end result is to Export to Outlook
to send out an email (that’s another nightmare). I have been able to create
the simple query with one field and then two fields using UNION. Now I want
to try and get three or more of the Yes fields.

When I try with multiple UNION I get the error message:
'The MS Office Access database engine could not find the object ". Make
sure the object exists & that you spell its name & the path correctly.' I am
copying & pasting from the SQL so it should be correct.

Other people have suggested using WHERE. I need to create something simple
as the person maintaining the mail list is not very adept with Access.

Use this calculated field and put criteria of 0
Expr1: Abs(Nz([SA],0)+Nz([PA],0)+Nz([RC],0)+Nz([CA],0))

"Thomas" wrote:

I have a mailing list where there are multiple yes/no fields. A record can
have multiple Yes or just one. One of the fields is a Text for emails. Not
all records have an email.

DB=Maillist
Fname-Text
Lname-Text
Email-Text
SA-Yes/No
PA-Yes/No
RC-Yes/No
CA-Yes/No

I am trying to create a query to find the emails address of records with a
yes in one or more of the field(s). The end result is to Export to Outlook
to send out an email (that’s another nightmare). I have been able to create
the simple query with one field and then two fields using UNION. Now I want
to try and get three or more of the Yes fields.

When I try with multiple UNION I get the error message:
'The MS Office Access database engine could not find the object ". Make
sure the object exists & that you spell its name & the path correctly.' I am
copying & pasting from the SQL so it should be correct.

Other people have suggested using WHERE. I need to create something simple
as the person maintaining the mail list is not very adept with Access.

What Karl suggests will certainly work, but what happens when you add another
field, say KY? The query will probably not work right unless you remember to
change it. Also any queries, forms or reports based on the table will need
updating.

The "proper" way to handle this would be to have another table linked to the
"Person" table. It would look something like:

Person Code
1 SA
1 PA
1 KY

The person field would be the foreign key field linked to the primary key
field of the Person table. If there is a "Code" for a person, that means Yes.
If not, assume No.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Thomas" wrote:

I have a mailing list where there are multiple yes/no fields. A record can
have multiple Yes or just one. One of the fields is a Text for emails. Not
all records have an email.

DB=Maillist
Fname-Text
Lname-Text
Email-Text
SA-Yes/No
PA-Yes/No
RC-Yes/No
CA-Yes/No

I am trying to create a query to find the emails address of records with a
yes in one or more of the field(s). The end result is to Export to Outlook
to send out an email (that’s another nightmare). I have been able to create
the simple query with one field and then two fields using UNION. Now I want
to try and get three or more of the Yes fields.

When I try with multiple UNION I get the error message:
'The MS Office Access database engine could not find the object ". Make
sure the object exists & that you spell its name & the path correctly.' I am
copying & pasting from the SQL so it should be correct.

Other people have suggested using WHERE. I need to create something simple
as the person maintaining the mail list is not very adept with Access.

On Thu, 9 Jul 2009 10:25:02 -0700, Thomas
wrote:

I have a mailing list where there are multiple yes/no fields. A record can
have multiple Yes or just one. One of the fields is a Text for emails. Not
all records have an email.

DB=Maillist
Fname-Text
Lname-Text
Email-Text
SA-Yes/No
PA-Yes/No
RC-Yes/No
CA-Yes/No

I am trying to create a query to find the emails address of records with a
yes in one or more of the field(s). The end result is to Export to Outlook
to send out an email (that’s another nightmare). I have been able to create
the simple query with one field and then two fields using UNION. Now I want
to try and get three or more of the Yes fields.

When I try with multiple UNION I get the error message:
'The MS Office Access database engine could not find the object ". Make
sure the object exists & that you spell its name & the path correctly.' I am
copying & pasting from the SQL so it should be correct.

Other people have suggested using WHERE. I need to create something simple
as the person maintaining the mail list is not very adept with Access.


Jerry's advice is good - this table structure IS WRONG, since it's encoding
data in fieldnames.

Try this query though:

SELECT FName, LName, Email
FROM Maillist
WHERE ([SA] OR [PA] OR [RC] OR [CA])
ORDER BY Lname, FName;

You can use [SA] = True OR [PA] = True etc. but it isn't necessary, since
these fields are already either TRUE or FALSE, and that's all that's required
for a search criterion.

You do not need a union query, and the user should probably not even *SEE*
this query (just use it).
--

John W. Vinson [MVP]