|
| 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
July 8th, 2009, 10:39 PM
posted to microsoft.public.access.queries
|
|||
|
|||
three query union.
Trying to make a three querry, but every time I try I get an error message
that MS Accesss DB can't find object ". I have 4 querries They all work & they all work in combinations of two, but not three? Thomas 
|
|
#2
July 8th, 2009, 10:48 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
three query union.
Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Thomas" wrote: Trying to make a three querry, but every time I try I get an error message that MS Accesss DB can't find object ". I have 4 querries They all work & they all work in combinations of two, but not three? Thomas
|
|
#3
July 8th, 2009, 10:52 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
three query union.
Thomas,
It will help us see what you see if you post the SQL of the query that is not working. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Thomas" wrote in message ... Trying to make a three querry, but every time I try I get an error message that MS Accesss DB can't find object ". I have 4 querries They all work & they all work in combinations of two, but not three? Thomas
|
|
#4
July 8th, 2009, 11:03 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
three query union.
SELECT [GENERAL MAILING LIST].FirstName, [GENERAL MAILING LIST].LastName,
[GENERAL MAILING LIST].EmailAddress, [GENERAL MAILING LIST].SA FROM [GENERAL MAILING LIST] WHERE ((([GENERAL MAILING LIST].EmailAddress) Like "*@*") AND (([GENERAL MAILING LIST].SA)=Yes)) UNION SELECT [GENERAL MAILING LIST].[FirstName], [GENERAL MAILING LIST].[LastName], [GENERAL MAILING LIST].[EmailAddress], [GENERAL MAILING LIST].[PG] FROM [GENERAL MAILING LIST] WHERE ((([GENERAL MAILING LIST].[EmailAddress]) Like "*@*") AND (([GENERAL MAILING LIST].[PG])=Yes)) UNION SELECT [GENERAL MAILING LIST].[FirstName], [GENERAL MAILING LIST].[LastName], [GENERAL MAILING LIST].[EmailAddress], [GENERAL MAILING LIST].[RC] FROM [GENERAL MAILING LIST] WHERE ((([GENERAL MAILING LIST].[EmailAddress]) Like "*@*") AND (([GENERAL MAILING LIST].[RC])=Yes)); "Jerry Whittle" wrote: Show us the SQL. Open the query in design view. Next go to View, SQL View and copy and past it here. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Thomas" wrote: Trying to make a three querry, but every time I try I get an error message that MS Accesss DB can't find object ". I have 4 querries They all work & they all work in combinations of two, but not three? Thomas
|
|
#5
July 8th, 2009, 11:18 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
three query union.
Thomas,
Where is the 4th query? Nothing seems out of place with this one. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Thomas" wrote in message ... SELECT [GENERAL MAILING LIST].FirstName, [GENERAL MAILING LIST].LastName, [GENERAL MAILING LIST].EmailAddress, [GENERAL MAILING LIST].SA FROM [GENERAL MAILING LIST] WHERE ((([GENERAL MAILING LIST].EmailAddress) Like "*@*") AND (([GENERAL MAILING LIST].SA)=Yes)) UNION SELECT [GENERAL MAILING LIST].[FirstName], [GENERAL MAILING LIST].[LastName], [GENERAL MAILING LIST].[EmailAddress], [GENERAL MAILING LIST].[PG] FROM [GENERAL MAILING LIST] WHERE ((([GENERAL MAILING LIST].[EmailAddress]) Like "*@*") AND (([GENERAL MAILING LIST].[PG])=Yes)) UNION SELECT [GENERAL MAILING LIST].[FirstName], [GENERAL MAILING LIST].[LastName], [GENERAL MAILING LIST].[EmailAddress], [GENERAL MAILING LIST].[RC] FROM [GENERAL MAILING LIST] WHERE ((([GENERAL MAILING LIST].[EmailAddress]) Like "*@*") AND (([GENERAL MAILING LIST].[RC])=Yes)); "Jerry Whittle" wrote: Show us the SQL. Open the query in design view. Next go to View, SQL View and copy and past it here. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Thomas" wrote: Trying to make a three querry, but every time I try I get an error message that MS Accesss DB can't find object ". I have 4 querries They all work & they all work in combinations of two, but not three? Thomas
|
|
#6
July 8th, 2009, 11:38 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
three query union.
I am only trying 3 querries. When I run this one 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.' Thomas "Gina Whipp" wrote: Thomas, Where is the 4th query? Nothing seems out of place with this one. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Thomas" wrote in message ... SELECT [GENERAL MAILING LIST].FirstName, [GENERAL MAILING LIST].LastName, [GENERAL MAILING LIST].EmailAddress, [GENERAL MAILING LIST].SA FROM [GENERAL MAILING LIST] WHERE ((([GENERAL MAILING LIST].EmailAddress) Like "*@*") AND (([GENERAL MAILING LIST].SA)=Yes)) UNION SELECT [GENERAL MAILING LIST].[FirstName], [GENERAL MAILING LIST].[LastName], [GENERAL MAILING LIST].[EmailAddress], [GENERAL MAILING LIST].[PG] FROM [GENERAL MAILING LIST] WHERE ((([GENERAL MAILING LIST].[EmailAddress]) Like "*@*") AND (([GENERAL MAILING LIST].[PG])=Yes)) UNION SELECT [GENERAL MAILING LIST].[FirstName], [GENERAL MAILING LIST].[LastName], [GENERAL MAILING LIST].[EmailAddress], [GENERAL MAILING LIST].[RC] FROM [GENERAL MAILING LIST] WHERE ((([GENERAL MAILING LIST].[EmailAddress]) Like "*@*") AND (([GENERAL MAILING LIST].[RC])=Yes)); "Jerry Whittle" wrote: Show us the SQL. Open the query in design view. Next go to View, SQL View and copy and past it here. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Thomas" wrote: Trying to make a three querry, but every time I try I get an error message that MS Accesss DB can't find object ". I have 4 querries They all work & they all work in combinations of two, but not three? Thomas
|
|
#7
July 9th, 2009, 12:14 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
three query union.
On Wed, 8 Jul 2009 14:39:01 -0700, Thomas
wrote: Trying to make a three querry, but every time I try I get an error message that MS Accesss DB can't find object ". I have 4 querries They all work & they all work in combinations of two, but not three? Thomas You don't appear to need a UNION query at all, since you're just pulling three subsets of data from the same table! Try SELECT [GENERAL MAILING LIST].FirstName, [GENERAL MAILING LIST].LastName, [GENERAL MAILING LIST].EmailAddress, [GENERAL MAILING LIST].SA FROM [GENERAL MAILING LIST] WHERE [GENERAL MAILING LIST].EmailAddress Like "*@*" AND ([GENERAL MAILING LIST].SA=True OR [GENERAL MAILING LIST].PG=True OR [GENERAL MAILING LIST].RC=True); I'm assuming that these are (non-normalized) yes/no checkbox fields in the table. -- John W. Vinson [MVP]
|
|
#8
July 9th, 2009, 12:28 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
three query union.
Now it asks for a Parameter for SA. Also using the UNION will keep out the
duplicates. Thomas "John W. Vinson" wrote: On Wed, 8 Jul 2009 14:39:01 -0700, Thomas wrote: Trying to make a three querry, but every time I try I get an error message that MS Accesss DB can't find object ". I have 4 querries They all work & they all work in combinations of two, but not three? Thomas You don't appear to need a UNION query at all, since you're just pulling three subsets of data from the same table! Try SELECT [GENERAL MAILING LIST].FirstName, [GENERAL MAILING LIST].LastName, [GENERAL MAILING LIST].EmailAddress, [GENERAL MAILING LIST].SA FROM [GENERAL MAILING LIST] WHERE [GENERAL MAILING LIST].EmailAddress Like "*@*" AND ([GENERAL MAILING LIST].SA=True OR [GENERAL MAILING LIST].PG=True OR [GENERAL MAILING LIST].RC=True); I'm assuming that these are (non-normalized) yes/no checkbox fields in the table. -- John W. Vinson [MVP]
|
|
#9
July 9th, 2009, 01:13 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
three query union.
Thomas,
Check all three queries to make sure the fields you specified are actually in the queries. Judging by your reply to John, the query with 'SA' may not have 'SA' in that query and/or may not be the field name in the table. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Thomas" wrote in message news  I am only trying 3 querries. When I run this one 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.' Thomas "Gina Whipp" wrote: Thomas, Where is the 4th query? Nothing seems out of place with this one. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Thomas" wrote in message ... SELECT [GENERAL MAILING LIST].FirstName, [GENERAL MAILING LIST].LastName, [GENERAL MAILING LIST].EmailAddress, [GENERAL MAILING LIST].SA FROM [GENERAL MAILING LIST] WHERE ((([GENERAL MAILING LIST].EmailAddress) Like "*@*") AND (([GENERAL MAILING LIST].SA)=Yes)) UNION SELECT [GENERAL MAILING LIST].[FirstName], [GENERAL MAILING LIST].[LastName], [GENERAL MAILING LIST].[EmailAddress], [GENERAL MAILING LIST].[PG] FROM [GENERAL MAILING LIST] WHERE ((([GENERAL MAILING LIST].[EmailAddress]) Like "*@*") AND (([GENERAL MAILING LIST].[PG])=Yes)) UNION SELECT [GENERAL MAILING LIST].[FirstName], [GENERAL MAILING LIST].[LastName], [GENERAL MAILING LIST].[EmailAddress], [GENERAL MAILING LIST].[RC] FROM [GENERAL MAILING LIST] WHERE ((([GENERAL MAILING LIST].[EmailAddress]) Like "*@*") AND (([GENERAL MAILING LIST].[RC])=Yes)); "Jerry Whittle" wrote: Show us the SQL. Open the query in design view. Next go to View, SQL View and copy and past it here. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Thomas" wrote: Trying to make a three querry, but every time I try I get an error message that MS Accesss DB can't find object ". I have 4 querries They all work & they all work in combinations of two, but not three? Thomas
|
|
#10
July 9th, 2009, 01:25 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
three query union.
On Wed, 8 Jul 2009 16:28:01 -0700, Thomas
wrote: Now it asks for a Parameter for SA. Your first query was SELECT [GENERAL MAILING LIST].FirstName, [GENERAL MAILING LIST].LastName, [GENERAL MAILING LIST].EmailAddress, [GENERAL MAILING LIST].SA FROM [GENERAL MAILING LIST] WHERE ((([GENERAL MAILING LIST].EmailAddress) Like "*@*") AND (([GENERAL MAILING LIST].SA)=Yes)) Is there in fact a field named SA in your table? If not you'll get the parameter prompt. Also using the UNION will keep out the duplicates. If there's only one record for each person, you won't get duplicates. Your UNION query will retrieve the same record three times if all three checkboxes are checked, but the WHERE clause will retrieve the record only once if any (or all) of the checkboxes are checked. If you can have the same name and email address multiple times in the table you can use SELECT DISTINCT to eliminate duplicates. -- John W. Vinson [MVP]
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
