If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
UNION, WHERE or Something else?
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. |
#3
|
|||
|
|||
UNION, WHERE or Something else?
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. |
#4
|
|||
|
|||
UNION, WHERE or Something else?
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] |
Thread Tools | |
Display Modes | |
|
|