Union query not working with *

[mbaycura]

Is there anything noticeable about this syntax that should give it reaon to
give the error can't find object?

SELECT [Ideas.*]
FROM [Res-project engineer]
UNION
SELECT [Ideas.*]
FROM [Res-mech design]
UNION
SELECT [Ideas.*]
FROM [Res-electrical design];

the queries reference [Res-project engineer], etc look like this

SELECT Ideas.*, Ideas.[Mechanical Design].Value
FROM Ideas
WHERE (((Ideas.[Mechanical Design].Value)=[Contacts].[ID]));

this query looks up in a table called "Ideas" any idea that has the contact
from the open form "Contacts" with the same ID in the mechanical design field
and works fine.

However, I can't get the union to work. Using the same exact syntax, if I
don't use the all * and in the mechanical design query just select a
particular field (like [Idea Title] which is a field in the table "Ideas"
individually then reference that in the union the union will work, but I
don't see why that would be.

I've tried different places to put the * and in different syntax combos but
could never get it to work, including using just the * by itself as suggested
by the syntax help, but nothing has worked when I have had the Ideas.* in the
sub-query.

Below here is the syntax that works when I just specify single fields in the
sub-query

SELECT [Idea Title]
FROM [Res-project engineer]
UNION
SELECT [Idea Title]
FROM [Res-mech design]
UNION SELECT [Idea Title]
FROM [Res-electrical design];

and from the sub-query

SELECT Ideas.[Idea Title], Ideas.[Mechanical Design].Value
FROM Ideas
WHERE (((Ideas.[Mechanical Design].Value)=[Contacts].[ID]));

 

[Stefan Hoffmann]

hi,

SELECT [Ideas.*]
FROM [Res-project engineer]
UNION
SELECT [Ideas.*]
FROM [Res-mech design]
UNION
SELECT [Ideas.*]
FROM [Res-electrical design];

Maybe a typo, but you use the alias [Ideas] in the select list, which is
not defined in your froms.


mfG
--> stefan <--

 

[John Spencer]

SELECT *
FROM [Res-project engineer]
UNION
SELECT *
FROM [Res-mech design]
UNION
SELECT *
FROM [Res-electrical design];


Change your source query to
SELECT Ideas.*
FROM Ideas
WHERE (((Ideas.[Mechanical Design].Value)=[Contacts].[ID]));

Although I don't understand why you don't just use something along the lines
of the following and skip the Union query entirely.

SELECT Ideas.*
FROM Ideas
WHERE Ideas.[Mechanical Design].Value=[Contacts].[ID]
OR Ideas.[Project Design].Value=[Contacts].[ID]
Or Ideas.[Electrical Design].Value=[Contacts].[ID]

Finally, I must confess to some confusion on your SQL
Contacts.Id and Ideas.[Mechanical Design].Value

I would normally expect to see
[Forms]![Contacts]![Id] as a reference to a control on an open form and
Ideas.[Mechanical Design] as a (table + Field Name)
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

hi,

SELECT [Ideas.*]
FROM [Res-project engineer]
UNION
SELECT [Ideas.*]
FROM [Res-mech design]
UNION
SELECT [Ideas.*]
FROM [Res-electrical design];

Maybe a typo, but you use the alias [Ideas] in the select list, which is
not defined in your froms.


mfG
--> stefan <--

 

[mbaycura]

Your SQL statement suggestion to avoid the union altogether was a great idea,
thanks! I had tried that line of thought initially, but I must have missed
something in my syntax or implimentation because I would get huge tables of
every combination and permutation of .values giving thousands of cases for
just a couple records.

I'm not too experienced in databases or access so I have some trouble
getting concept into implementaions.

and to that end, I miss-spoke in my original question, Contacts is the
table, I haven't integrated the query into the form yet.

thanks again.