three query union.

  • Thread starter Thread starter Thomas
  • Start date Start date
T

Thomas

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
 
Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here.
 
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));
 
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 said:
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 said:
Show us the SQL. Open the query in design view. Next go to View, SQL
View
and copy and past it here.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.


:

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
Click to expand...
Click to expand...
Click to expand...
 
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
Click to expand...

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.
 
Now it asks for a Parameter for SA. Also using the UNION will keep out the
duplicates.

Thomas

John W. Vinson said:
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
Click to expand...

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.
Click to expand...
 
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 said:
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 said:
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 said:
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));

:

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.


:

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
Click to expand...
Click to expand...
Click to expand...
 
Now it asks for a Parameter for SA.
Click to expand...

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.
Click to expand...

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.
 
I don't understand why you are using a union query at all. This query
would apparently give you the same results with less trouble.

SELECT [GENERAL MAILING LIST].FirstName
, [GENERAL MAILING LIST].LastName
, [GENERAL MAILING LIST].EmailAddress
, True as ReturnTrue
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)


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

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 said:
Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here.
Click to expand...
Click to expand...
 
Now it asks for Paramater General. I'm I doing something wrong when I try to
create a new query?

Think I will start a new post & start w/what I'm trying to do. Get a fresh
start to my problem.

John W. Vinson said:
Now it asks for a Parameter for SA.
Click to expand...

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.
Click to expand...

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.
Click to expand...
 
Thank you for your help. I've reposted with more info. It is titled:

UNION, WHERE or Something else?

If you have suggestion would be greatly appreciated.

Thomas

John W. Vinson said:
Now it asks for a Parameter for SA.
Click to expand...

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.
Click to expand...

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.
Click to expand...
 
Thank you for your help. I've reposted with more info. It is titled:

UNION, WHERE or Something else?

If you have suggestion would be greatly appreciated.

Thomas
 
Back
Top