By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,888 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

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

P: n/a
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=GetCurrentVehicleJobI D()) AND
(tblAuth.AuthCatID)<>2;

UNION SELECT [ProxyFName] & " " & [ProxyLName] AS Recipient FROM
tblVehicleJobs INNER JOIN tblProxies ON tblVehicleJobs.ProxyID =
tblProxies.ProxyID WHERE
tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID ();
Jul 1 '07 #1
Share this Question
Share on Google+
27 Replies


P: n/a
Why do you want to make a table?

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


"MLH" <CR**@NorthState.netwrote in message
news:e5********************************@4ax.com...
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=GetCurrentVehicleJobI D()) AND
(tblAuth.AuthCatID)<>2;

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

Jul 1 '07 #2

P: n/a
MLH <CR**@NorthState.netwrote in
news:e5********************************@4ax.com:
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=GetCurrentVehicleJobI D())
AND (tblAuth.AuthCatID)<>2;

UNION SELECT [ProxyFName] & " " & [ProxyLName] AS Recipient
FROM tblVehicleJobs INNER JOIN tblProxies ON
tblVehicleJobs.ProxyID = tblProxies.ProxyID WHERE
tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID ();
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

Jul 1 '07 #3

P: n/a
SELECT 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=GetCurrentVehicleJobI D()) AND
(tblAuth.AuthCatID)<>2;

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

MLH wrote:
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=GetCurrentVehicleJobI D()) AND
(tblAuth.AuthCatID)<>2;

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

P: n/a
On Jul 1, 11:31 am, MLH <C...@NorthState.netwrote:
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
CD********@FortuneJames.com

Jul 1 '07 #5

P: n/a
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.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxx

On Sun, 01 Jul 2007 16:16:00 GMT, "Steve" <so***@private.emailaddress>
wrote:
>Why do you want to make a table?

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


"MLH" <CR**@NorthState.netwrote in message
news:e5********************************@4ax.com.. .
>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=GetCurrentVehicleJobI D();

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

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

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

UNION SELECT [ProxyFName] & " " & [ProxyLName] AS Recipient FROM
tblVehicleJobs INNER JOIN tblProxies ON tblVehicleJobs.ProxyID =
tblProxies.ProxyID WHERE
tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobI D();
Jul 1 '07 #6

P: n/a
MLH
Alright! Now that's what I'm talkin' about. Thanks, John.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxx
On Sun, 01 Jul 2007 13:38:35 -0400, John Winterbottom
<jo*****@rogers.cawrote:
>SELECT Recipient
into newtable
from
(
SELECT [AddnlOwnrFName] & " " & [AddnlOwnrLName] AS Recipient FROM
tblVehicleJobs INNER JOIN tblAddnlOwnrs ON tblVehicleJobs.VehicleJobID
= tblAddnlOwnrs.VehicleJobID WHERE
tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobI D();

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

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

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

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

MLH wrote:
>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=GetCurrentVehicleJobI D();

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

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

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

UNION SELECT [ProxyFName] & " " & [ProxyLName] AS Recipient FROM
tblVehicleJobs INNER JOIN tblProxies ON tblVehicleJobs.ProxyID =
tblProxies.ProxyID WHERE
tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobI D();
Jul 1 '07 #7

P: n/a
MLH
Oops. I might-a-spoke too fast. A97 gives me a syntax error in FROM
clause. Is the SQL A97 compatible?
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxx

On Sun, 01 Jul 2007 19:22:14 -0400, MLH <CR**@NorthState.netwrote:
>Alright! Now that's what I'm talkin' about. Thanks, John.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxx
On Sun, 01 Jul 2007 13:38:35 -0400, John Winterbottom
<jo*****@rogers.cawrote:
>>SELECT Recipient
into newtable
from
(
SELECT [AddnlOwnrFName] & " " & [AddnlOwnrLName] AS Recipient FROM
tblVehicleJobs INNER JOIN tblAddnlOwnrs ON tblVehicleJobs.VehicleJobID
= tblAddnlOwnrs.VehicleJobID WHERE
tblVehicleJobs.VehicleJobID=GetCurrentVehicleJob ID();

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

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

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

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

MLH wrote:
>>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=GetCurrentVehicleJob ID();

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

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

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

UNION SELECT [ProxyFName] & " " & [ProxyLName] AS Recipient FROM
tblVehicleJobs INNER JOIN tblProxies ON tblVehicleJobs.ProxyID =
tblProxies.ProxyID WHERE
tblVehicleJobs.VehicleJobID=GetCurrentVehicleJob ID();
Jul 1 '07 #8

P: n/a
MLH
Am having trouble getting Access 97 to accept the suggested syntax.

What's the . UQ; all about, anyway?
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxx
On Sun, 01 Jul 2007 12:58:21 -0700, CD********@FortuneJames.com wrote:
>On Jul 1, 11:31 am, MLH <C...@NorthState.netwrote:
>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
CD********@FortuneJames.com
Jul 2 '07 #9

P: n/a
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
re******@pcdatasheet.com

"MLH" <CR**@NorthState.netwrote in message
news:tc********************************@4ax.com...
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.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxx

On Sun, 01 Jul 2007 16:16:00 GMT, "Steve" <so***@private.emailaddress>
wrote:
>>Why do you want to make a table?

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


"MLH" <CR**@NorthState.netwrote in message
news:e5********************************@4ax.com. ..
>>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=GetCurrentVehicleJob ID();

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

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

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

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

Jul 2 '07 #10

P: n/a
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>
Jul 2 '07 #11

P: n/a
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
Jul 2 '07 #12

P: n/a
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)
Jul 2 '07 #13

P: n/a
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.
Jul 2 '07 #14

P: n/a
"MLH" <CR**@NorthState.netwrote in message
news:f8********************************@4ax.com...
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)
It didn't take me long to find this:

http://forums.devarticles.com/micros...ble-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

Jul 2 '07 #15

P: n/a
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.
Jul 2 '07 #16

P: n/a
On Jul 1, 8:53 pm, MLH <C...@NorthState.netwrote:
Am having trouble getting Access 97 to accept the suggested syntax.

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

On Sun, 01 Jul 2007 12:58:21 -0700, CDMAPos...@FortuneJames.com wrote:
On Jul 1, 11:31 am, MLH <C...@NorthState.netwrote:
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;
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
CD********@FortuneJames.com

Jul 2 '07 #17

P: n/a
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.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
Jul 3 '07 #18

P: n/a
"Tim Marshall" <TI****@PurplePandaChasers.Moertheriumwrote in message
news:f6**********@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.

Jul 3 '07 #19

P: n/a
On Jul 2, 3:14 pm, CDMAPos...@FortuneJames.com wrote:
On Jul 1, 8:53 pm, MLH <C...@NorthState.netwrote:
Am having trouble getting Access 97 to accept the suggested syntax.
What's the . UQ; all about, anyway?
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxx
On Sun, 01 Jul 2007 12:58:21 -0700, CDMAPos...@FortuneJames.com wrote:
>On Jul 1, 11:31 am, MLH <C...@NorthState.netwrote:
>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;

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;
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.
Jul 4 '07 #20

P: n/a
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.
Jul 4 '07 #21

P: n/a
On Jul 4, 6:06 am, Yitzak <terrysha...@yahoo.co.ukwrote:
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.
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
CD********@FortuneJames.com

Jul 5 '07 #22

P: n/a
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=GetCurrentVehicleJobI D()) 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.
Jul 6 '07 #23

P: n/a
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.
Jul 6 '07 #24

P: n/a
On Jul 6, 11:55 am, MLH <C...@NorthState.netwrote:
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.
In:

http://groups.google.com/group/comp....fb3cfef26cdb9b

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
CD********@FortuneJames.com

Jul 6 '07 #25

P: n/a
MLH
On Fri, 06 Jul 2007 12:47:00 -0700, CD********@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.
Jul 7 '07 #26

P: n/a
On Jul 7, 8:35 am, MLH <C...@NorthState.netwrote:
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

Jul 7 '07 #27

P: n/a
On Jul 7, 10:32 am, lyle <lyle.fairfi...@gmail.comwrote:
On Jul 7, 8:35 am, MLH <C...@NorthState.netwrote:
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
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
CD********@FortuneJames.com

Jul 8 '07 #28

This discussion thread is closed

Replies have been disabled for this discussion.