Can I turn a union query into a make-table query?

[MLH]

How can I turn the following into a make-table query?

SELECT [AddnlOwnrFName] & " " & [AddnlOwnrLName] AS Recipient FROM
tblVehicleJobs INNER JOIN tblAddnlOwnrs ON tblVehicleJobs.VehicleJobID
= tblAddnlOwnrs.VehicleJobID WHERE
tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID();

UNION SELECT [LienHolderName] AS Recipient FROM tblVehicleJobs INNER
JOIN tblLienHolders ON tblVehicleJobs.VehicleJobID =
tblLienHolders.VehicleJobID WHERE
tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID();

UNION SELECT [OwnerFName] & " " & [OwnerLName] AS Recipient FROM
tblVehicleJobs INNER JOIN tblOwners ON tblVehicleJobs.OwnerID =
tblOwners.OwnerID WHERE
tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID();

UNION SELECT IIf(IsNull([AuthCompany]),[AuthFName] & " " &
[AuthLName],[AuthCompany]) AS Recipient FROM tblAuth INNER JOIN
tblVehicleJobs ON tblAuth.AuthID = tblVehicleJobs.AuthID WHERE
(tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID()) AND
(tblAuth.AuthCatID)<>2;

UNION SELECT [ProxyFName] & " " & [ProxyLName] AS Recipient FROM
tblVehicleJobs INNER JOIN tblProxies ON tblVehicleJobs.ProxyID =
tblProxies.ProxyID WHERE
tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID();

[Steve]

Why do you want to make a table?

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
reso...@pcdatasheet.com

"MLH" <CR...@NorthState.net> wrote in message
news:e5if83p2la7vndfce...@4ax.com...

[Bob Quintal]

MLH <CR...@NorthState.net> wrote in
news:e5if83p2la7vndfce...@4ax.com:

save it as a querydef. Then use the make table query wizard
sourced from that querydef.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Arch]

It's better when you at least pretend to try to help in order to post
your advertising.

[John Winterbottom]

SELECT Recipient
into newtable
from
(

)

[CDMAP...@fortunejames.com]

On Jul 1, 11:31 am, MLH <C...@NorthState.net> wrote:
> How can I turn the following into a make-table query?
>
> SELECT [AddnlOwnrFName] & " " & [AddnlOwnrLName] AS Recipient FROM

> ...

Note: If you're using A97 try changing John's syntax to:

SELECT Recipient
into newtable
from

[
SELECT ...
UNION SELECT ...
UNION SELECT ...
UNION SELECT ...
UNION SELECT ...
]. UQ;

James A. Fortune
CDMAP...@FortuneJames.com

[MLH]

Good question. I really don't want to. But I think I must.
When I use the Union query in the query builder and
click the BANG button to run it - it runs fine, returning
the dynaset I expect. But when I use it with DAO, trying
to walk the records returned - it collapses with an error
saying "Undefined function 'GetCurrentVehicleJobID' in
expression" and I've been unable to get around it. The
SQL is sound but somehow DAO access to it just isn't
working. I could post my attempt, if you like.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

On Sun, 01 Jul 2007 16:16:00 GMT, "Steve" <so...@private.emailaddress>
wrote:

[MLH]

Alright! Now that's what I'm talkin' about. Thanks, John.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

[MLH]

Oops. I might-a-spoke too fast. A97 gives me a syntax error in FROM
clause. Is the SQL A97 compatible?
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

[MLH]

Am having trouble getting Access 97 to accept the suggested syntax.

What's the . UQ; all about, anyway?
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

[Steve]

What exactly is the dynaset you expect and why do you want to make a table
of that data?

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
reso...@pcdatasheet.com

"MLH" <CR...@NorthState.net> wrote in message

news:tcdg83pav3q2pi977...@4ax.com...

[MLH]

On Mon, 02 Jul 2007 01:07:19 GMT, "Steve" <so...@private.emailaddress>
wrote:

>What exactly is the dynaset you expect

The same dynaset returned when dbl-clicking
saved qruery in query tab of the database window.

>and why do you want to make a table of that data?

Good question. I really don't want to. But I think I must.
When I use the Union query in the query builder and
click the BANG button to run it - it runs fine, returning
the dynaset I expect. But when I use it with DAO, trying
to walk the records returned - it collapses with an error
saying "Undefined function 'GetCurrentVehicleJobID' in
expression" and I've been unable to get around it. The
SQL is sound but somehow DAO access to it just isn't
working. I could post my attempt, if you like.

<snip>

[Tim Marshall]

Steve wrote:

> What exactly is the dynaset you expect and why do you want to make a table
> of that data?

>>the dynaset I expect. But when I use it with DAO, trying

>>to walk the records returned - it collapses with an error
>>saying "Undefined function 'GetCurrentVehicleJobID' in
>>expression" and I've been unable to get around it. The

If it runs on the query builder, it will run if properly constructed
using DAO to create a recordset. MLH needs to slow down and look at his
problem, but as usual, doesn't seem to have the patience. There is a
problem with what he's doing and a careful google will show exactly what
it is.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[MLH]

Tim, why don't you point me in the right
direction and tell me what to google for?
I get nearly 11-thousand hits searching
on my question.

Results 1 - 100 of about 10,900 for turn a union query into a
make-table query. (0.22 seconds)

[MLH]

Kind-a-like a riddle, huh?

There was a young man named Tim,
Who responded to postings at his whim.
Much like a dancer, rather than answer
The help he proviced was slim.

Bye Tim.

[Keith Wilby]

"MLH" <CR...@NorthState.net> wrote in message

news:f8hh83phhqd0spl9d...@4ax.com...

It didn't take me long to find this:

http://forums.devarticles.com/microsoft-access-development-49/combine-query-to-make-table-10008.html

"I believe your error is caused by trying to perform two actions at the same
time. Joining related records (Union) and then trying to insert those
records into a table (Append). You can't append records based on a query
that hasn't been run yet. My suggestion is to have the two queries. The
Union query and the Append Query. That way when you run the Append Query,
the Union Query will run as part of the Append Query action."

Keith.
www.keithwilby.com

[MLH]

Thanks for the tip, Keith.

I'm lucky you found it. The URL you recommended
was not in my first 100 google hits. You saved me
HOURS of searching. Hope I'm able to reciprocate
sometime.

[CDMAP...@fortunejames.com]

MLH,

You should replace the three dots with the same text that John
used :-). I tried the syntax I posted and it worked fine in A97. To
make it more robust you can even prepend UQ to Recipient:

SELECT UQ.Recipient

into newtable
from
[
SELECT ...

...
]. UQ;

The UQ part (stands for union query here) is any name you want to give
to the query within the square brackets (you did use square brackets,
right?) for reference within the same query. My only advice is to
make sure the part within the square brackets runs on its own first
and that it displays a field called Recipient.

James A. Fortune
CDMAP...@FortuneJames.com

[Tim Marshall]

MLH wrote:

> There was a young man named Tim,
> Who responded to postings at his whim.
> Much like a dancer, rather than answer
> The help he proviced was slim.

Actually, you may recall, I've helped you in the past a number of times.
But you spend so much time crying for help to pull up your pants
instead of learning to bend over and do it yourself, you've doubtless
forgotten.

In my opinion, you behave here like the spoiled kid who never gets
enough candy handed to him and is always jumping up and down demanding
more without learning how to reach for it himself.

I don't have your poetic gift to make up a limerick about someone who
refuses to use his help file or google. I've got you kf'ed on my main
machine, time to add you to this one.

[Keith Wilby]

"Tim Marshall" <TIMMY!@PurplePandaChasers.Moertherium> wrote in message
news:f6cmt8$c7m$1...@coranto.ucs.mun.ca...

>
> Actually, you may recall, I've helped you in the past a number of times.

The irony/sarcasm of "It didn't take me long to find this" in my reply was
lost on him too ;-)

Keith.

[lyle]

TTBOMK Nested "[" or "]" delimiters cause any query to fail.

I recall seeing cases where Access/Jet will accept When_I_Hallucinate
for [When I Hallucinate] ... maybe.

Access/Jet !!!might!!! also accept 1, where [When I Hallucinate] is
the first field in a table or query.

The lower case tilde "`" can be used in place of both [ and ] but that
doesn't help with nesting problems. That is, nested `s and [s are not
permitted either.

[Yitzak]

Have you tried saving the SQL as a query if possible (not paramitised)

Then just make the make table query from a simple select of the new
query.

[CDMAP...@fortunejames.com]

It's good to know that the ['s can't be nested (thanks, Lyle). MLH's
field names do not contain any spaces so there was no reason to nest
the ['s. Your idea looks like the safest one. I was simply trying to
make John's solution work for A97.

James A. Fortune
CDMAP...@FortuneJames.com

[MLH]

Here's what I used:

SELECT UQ.Recipient
into newtable
from
[

SELECT AddnlOwnrFName & " " & AddnlOwnrLName AS Recipient FROM

tblVehicleJobs INNER JOIN tblAddnlOwnrs ON tblVehicleJobs.VehicleJobID
= tblAddnlOwnrs.VehicleJobID WHERE
tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID();

UNION SELECT LienHolderName AS Recipient FROM tblVehicleJobs INNER
JOIN tblLienHolders ON tblVehicleJobs.VehicleJobID =
tblLienHolders.VehicleJobID WHERE
tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID();

UNION SELECT OwnerFName & " " & OwnerLName AS Recipient FROM
tblVehicleJobs INNER JOIN tblOwners ON tblVehicleJobs.OwnerID =
tblOwners.OwnerID WHERE
tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID();

UNION SELECT IIf(IsNull(AuthCompany),AuthFName & " " &
AuthLName,AuthCompany) AS Recipient FROM tblAuth INNER JOIN

tblVehicleJobs ON tblAuth.AuthID = tblVehicleJobs.AuthID WHERE
(tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID()) AND
(tblAuth.AuthCatID)<>2;

UNION SELECT ProxyFName & " " & ProxyLName AS Recipient FROM
tblVehicleJobs INNER JOIN tblProxies ON tblVehicleJobs.ProxyID =
tblProxies.ProxyID WHERE
tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID()

], UQ;

I must be doing something wrong. For the life of me, I cannot
figure out what it is. I even took out all the nested square brackets.
It still isn't working.

[MLH]

And I might point out that everything between the square
brackets works when pasted into a query and run. So my
problem is limited to that I've placed outside the square
brackets - before and after.

[CDMAP...@fortunejames.com]

In:

http://groups.google.com/group/comp.databases.ms-access/msg/80fb3cfef26cdb9b

Lyle said:

"You use Square Brackets and you think this is Truth.
Then you find there are other solutions:

SELECT * FROM All_Students
SELECT * FROM `All Students`

And you wonder .... "

Perhaps there are indeed other solutions. Did you try Yitzak's idea?

I'm running out of suggestions. I remember that the union query I
made didn't have all the ;'s between, but I'm reaching for straws
here. Your query looks like it should work. Can you get a simple
union query to work with the same syntax? I'm using SR-1.

James A. Fortune
CDMAP...@FortuneJames.com

[MLH]

On Fri, 06 Jul 2007 12:47:00 -0700, CDMAP...@FortuneJames.com wrote:

>Perhaps there are indeed other solutions.

There ARE other solns. I could use separate queries - the first
would be a make table and the others would be append queries.

>Did you try Yitzak's idea?

Not yet. Sounds like it might work. Right now I'm more interested in
why I cannot reproduce on SR-2 what you are doing on SR-1.

>
>I'm running out of suggestions. I remember that the union query I
>made didn't have all the ;'s between, but I'm reaching for straws
>here. Your query looks like it should work. Can you get a simple
>union query to work with the same syntax? I'm using SR-1.

This IS a simple union query. Do you mean a SAVED union query?
If that's what you're asking - yes, I can:

SELECT [UQ].Recipient INTO TargetTbl FROM UQ;

where UQ is the 'simple union query' you are talking about. Of course,
it is a SAVED query. Nothing difficult about that. But defining the
SQL from within code that will do the same thing doesn't seem to be
possible with Access 97 SR-2.

James, I appreciate you trying to help. Perhaps this is an
installation-specific anomoly that cannot be resolved. Its unlikely,
but possible. I am curious about the syntax J Winterbottom proposed
and you modified...

SELECT UQ.Recipient
into newtable
from
[

Blah Blah Blah
], UQ;

I'm not familiar with it. But it looks promising. If the stuff between
the square brackets defines the union query and UQ is NOT saved
as UQ in the query tab of the database window - is this syntax still
supposed to work. In other words - you did not have UQ saved as
a query when you did your testing, did you? Like you, am grasping
for straws here.

[lyle]

On Jul 7, 8:35 am, MLH <C...@NorthState.net> wrote:

> On Fri, 06 Jul 2007 12:47:00 -0700, CDMAPos...@FortuneJames.com wrote:
> >Perhaps there are indeed other solutions.
>
> There ARE other solns. I could use separate queries - the first
> would be a make table and the others would be append queries.

Have you considered trying the correct syntax (as James posted)?

It's

right square bracket
dot
space
alias

as in
]. UQ

[CDMAP...@fortunejames.com]

I must have been hallucinating. That's a better excuse than admitting
that an old man has sharper eyes than I do :-). The font I'm looking
at makes the , and . look almost the same.

James A. Fortune
CDMAP...@FortuneJames.com