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.V ehicleJobID WHERE
tblVehicleJobs. VehicleJobID=Ge tCurrentVehicle JobID();

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

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

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

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

[Steve]

Why do you want to make a table?

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


"MLH" <CR**@NorthStat e.netwrote in message
news:e5******** *************** *********@4ax.c om...

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.V ehicleJobID WHERE
tblVehicleJobs. VehicleJobID=Ge tCurrentVehicle JobID();

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

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

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

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

[Bob Quintal]

MLH <CR**@NorthStat e.netwrote in
news:e5******** *************** *********@4ax.c om:

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.V ehicleJobID WHERE
tblVehicleJobs. VehicleJobID=Ge tCurrentVehicle JobID();

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

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

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

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

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

[John Winterbottom]

SELECT Recipient
into newtable
from
(
SELECT [AddnlOwnrFName] & " " & [AddnlOwnrLName] AS Recipient FROM
tblVehicleJobs INNER JOIN tblAddnlOwnrs ON tblVehicleJobs. VehicleJobID
= tblAddnlOwnrs.V ehicleJobID WHERE
tblVehicleJobs. VehicleJobID=Ge tCurrentVehicle JobID();

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

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

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

UNION SELECT [ProxyFName] & " " & [ProxyLName] AS Recipient FROM
tblVehicleJobs INNER JOIN tblProxies ON tblVehicleJobs. ProxyID =
tblProxies.Prox yID WHERE
tblVehicleJobs. VehicleJobID=Ge tCurrentVehicle JobID()
)

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.V ehicleJobID WHERE
tblVehicleJobs. VehicleJobID=Ge tCurrentVehicle JobID();

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

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

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

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

[CDMAPoster]

On Jul 1, 11:31 am, MLH <C...@NorthStat e.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********@Fort uneJames.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 'GetCurrentVehi cleJobID' 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.

xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxx

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******@pcdat asheet.com


"MLH" <CR**@NorthStat e.netwrote in message
news:e5******* *************** **********@4ax. com...

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

SELECT [AddnlOwnrFName] & " " & [AddnlOwnrLName] AS Recipient FROM
tblVehicleJo bs INNER JOIN tblAddnlOwnrs ON tblVehicleJobs. VehicleJobID
= tblAddnlOwnrs.V ehicleJobID WHERE
tblVehicleJobs .VehicleJobID=G etCurrentVehicl eJobID();

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

UNION SELECT [OwnerFName] & " " & [OwnerLName] AS Recipient FROM
tblVehicleJo bs INNER JOIN tblOwners ON tblVehicleJobs. OwnerID =
tblOwners.Owne rID WHERE
tblVehicleJobs .VehicleJobID=G etCurrentVehicl eJobID();

UNION SELECT IIf(IsNull([AuthCompany]),[AuthFName] & " " &
[AuthLName],[AuthCompany]) AS Recipient FROM tblAuth INNER JOIN
tblVehicleJo bs ON tblAuth.AuthID = tblVehicleJobs. AuthID WHERE
(tblVehicleJob s.VehicleJobID= GetCurrentVehic leJobID()) AND
(tblAuth.AuthC atID)<>2;

UNION SELECT [ProxyFName] & " " & [ProxyLName] AS Recipient FROM
tblVehicleJo bs INNER JOIN tblProxies ON tblVehicleJobs. ProxyID =
tblProxies.Pro xyID WHERE
tblVehicleJobs .VehicleJobID=G etCurrentVehicl eJobID();

[MLH]

Alright! Now that's what I'm talkin' about. Thanks, John.
xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx x
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
tblVehicleJo bs INNER JOIN tblAddnlOwnrs ON tblVehicleJobs. VehicleJobID
= tblAddnlOwnrs.V ehicleJobID WHERE
tblVehicleJobs .VehicleJobID=G etCurrentVehicl eJobID();

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

UNION SELECT [OwnerFName] & " " & [OwnerLName] AS Recipient FROM
tblVehicleJo bs INNER JOIN tblOwners ON tblVehicleJobs. OwnerID =
tblOwners.Owne rID WHERE
tblVehicleJobs .VehicleJobID=G etCurrentVehicl eJobID();

UNION SELECT IIf(IsNull([AuthCompany]),[AuthFName] & " " &
[AuthLName],[AuthCompany]) AS Recipient FROM tblAuth INNER JOIN
tblVehicleJo bs ON tblAuth.AuthID = tblVehicleJobs. AuthID WHERE
(tblVehicleJob s.VehicleJobID= GetCurrentVehic leJobID()) AND
(tblAuth.AuthC atID)<>2;

UNION SELECT [ProxyFName] & " " & [ProxyLName] AS Recipient FROM
tblVehicleJo bs INNER JOIN tblProxies ON tblVehicleJobs. ProxyID =
tblProxies.Pro xyID WHERE
tblVehicleJobs .VehicleJobID=G etCurrentVehicl eJobID()
)

MLH wrote:

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

SELECT [AddnlOwnrFName] & " " & [AddnlOwnrLName] AS Recipient FROM
tblVehicleJo bs INNER JOIN tblAddnlOwnrs ON tblVehicleJobs. VehicleJobID
= tblAddnlOwnrs.V ehicleJobID WHERE
tblVehicleJobs .VehicleJobID=G etCurrentVehicl eJobID();

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

UNION SELECT [OwnerFName] & " " & [OwnerLName] AS Recipient FROM
tblVehicleJo bs INNER JOIN tblOwners ON tblVehicleJobs. OwnerID =
tblOwners.Owne rID WHERE
tblVehicleJobs .VehicleJobID=G etCurrentVehicl eJobID();

UNION SELECT IIf(IsNull([AuthCompany]),[AuthFName] & " " &
[AuthLName],[AuthCompany]) AS Recipient FROM tblAuth INNER JOIN
tblVehicleJo bs ON tblAuth.AuthID = tblVehicleJobs. AuthID WHERE
(tblVehicleJob s.VehicleJobID= GetCurrentVehic leJobID()) AND
(tblAuth.AuthC atID)<>2;

UNION SELECT [ProxyFName] & " " & [ProxyLName] AS Recipient FROM
tblVehicleJo bs INNER JOIN tblProxies ON tblVehicleJobs. ProxyID =
tblProxies.Pro xyID WHERE
tblVehicleJobs .VehicleJobID=G etCurrentVehicl eJobID();

[MLH]

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

On Sun, 01 Jul 2007 19:22:14 -0400, MLH <CR**@NorthStat e.netwrote:

>Alright! Now that's what I'm talkin' about. Thanks, John.
xxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xx
On Sun, 01 Jul 2007 13:38:35 -0400, John Winterbottom
<jo*****@roger s.cawrote:

>>SELECT Recipient
into newtable
from
(
SELECT [AddnlOwnrFName] & " " & [AddnlOwnrLName] AS Recipient FROM
tblVehicleJob s INNER JOIN tblAddnlOwnrs ON tblVehicleJobs. VehicleJobID
= tblAddnlOwnrs.V ehicleJobID WHERE
tblVehicleJob s.VehicleJobID= GetCurrentVehic leJobID();

UNION SELECT [LienHolderName] AS Recipient FROM tblVehicleJobs INNER
JOIN tblLienHolders ON tblVehicleJobs. VehicleJobID =
tblLienHolder s.VehicleJobID WHERE
tblVehicleJob s.VehicleJobID= GetCurrentVehic leJobID();

UNION SELECT [OwnerFName] & " " & [OwnerLName] AS Recipient FROM
tblVehicleJob s INNER JOIN tblOwners ON tblVehicleJobs. OwnerID =
tblOwners.Own erID WHERE
tblVehicleJob s.VehicleJobID= GetCurrentVehic leJobID();

UNION SELECT IIf(IsNull([AuthCompany]),[AuthFName] & " " &
[AuthLName],[AuthCompany]) AS Recipient FROM tblAuth INNER JOIN
tblVehicleJob s ON tblAuth.AuthID = tblVehicleJobs. AuthID WHERE
(tblVehicleJo bs.VehicleJobID =GetCurrentVehi cleJobID()) AND
(tblAuth.Auth CatID)<>2;

UNION SELECT [ProxyFName] & " " & [ProxyLName] AS Recipient FROM
tblVehicleJob s INNER JOIN tblProxies ON tblVehicleJobs. ProxyID =
tblProxies.Pr oxyID WHERE
tblVehicleJob s.VehicleJobID= GetCurrentVehic leJobID()
)

MLH wrote:

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

SELECT [AddnlOwnrFName] & " " & [AddnlOwnrLName] AS Recipient FROM
tblVehicleJob s INNER JOIN tblAddnlOwnrs ON tblVehicleJobs. VehicleJobID
= tblAddnlOwnrs.V ehicleJobID WHERE
tblVehicleJob s.VehicleJobID= GetCurrentVehic leJobID();

UNION SELECT [LienHolderName] AS Recipient FROM tblVehicleJobs INNER
JOIN tblLienHolders ON tblVehicleJobs. VehicleJobID =
tblLienHolder s.VehicleJobID WHERE
tblVehicleJob s.VehicleJobID= GetCurrentVehic leJobID();

UNION SELECT [OwnerFName] & " " & [OwnerLName] AS Recipient FROM
tblVehicleJob s INNER JOIN tblOwners ON tblVehicleJobs. OwnerID =
tblOwners.Own erID WHERE
tblVehicleJob s.VehicleJobID= GetCurrentVehic leJobID();

UNION SELECT IIf(IsNull([AuthCompany]),[AuthFName] & " " &
[AuthLName],[AuthCompany]) AS Recipient FROM tblAuth INNER JOIN
tblVehicleJob s ON tblAuth.AuthID = tblVehicleJobs. AuthID WHERE
(tblVehicleJo bs.VehicleJobID =GetCurrentVehi cleJobID()) AND
(tblAuth.Auth CatID)<>2;

UNION SELECT [ProxyFName] & " " & [ProxyLName] AS Recipient FROM
tblVehicleJob s INNER JOIN tblProxies ON tblVehicleJobs. ProxyID =
tblProxies.Pr oxyID WHERE
tblVehicleJob s.VehicleJobID= GetCurrentVehic leJobID();

[MLH]

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

What's the . UQ; all about, anyway?
xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxx
On Sun, 01 Jul 2007 12:58:21 -0700, CD********@Fort uneJames.com wrote:

>On Jul 1, 11:31 am, MLH <C...@NorthStat e.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********@For tuneJames.com

[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
re******@pcdata sheet.com

"MLH" <CR**@NorthStat e.netwrote in message
news:tc******** *************** *********@4ax.c om...

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 'GetCurrentVehi cleJobID' 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.

xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxx

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
Application s
re******@pcda tasheet.com


"MLH" <CR**@NorthStat e.netwrote in message
news:e5****** *************** ***********@4ax .com...

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

SELECT [AddnlOwnrFName] & " " & [AddnlOwnrLName] AS Recipient FROM
tblVehicleJob s INNER JOIN tblAddnlOwnrs ON tblVehicleJobs. VehicleJobID
= tblAddnlOwnrs.V ehicleJobID WHERE
tblVehicleJob s.VehicleJobID= GetCurrentVehic leJobID();

UNION SELECT [LienHolderName] AS Recipient FROM tblVehicleJobs INNER
JOIN tblLienHolders ON tblVehicleJobs. VehicleJobID =
tblLienHolder s.VehicleJobID WHERE
tblVehicleJob s.VehicleJobID= GetCurrentVehic leJobID();

UNION SELECT [OwnerFName] & " " & [OwnerLName] AS Recipient FROM
tblVehicleJob s INNER JOIN tblOwners ON tblVehicleJobs. OwnerID =
tblOwners.Own erID WHERE
tblVehicleJob s.VehicleJobID= GetCurrentVehic leJobID();

UNION SELECT IIf(IsNull([AuthCompany]),[AuthFName] & " " &
[AuthLName],[AuthCompany]) AS Recipient FROM tblAuth INNER JOIN
tblVehicleJob s ON tblAuth.AuthID = tblVehicleJobs. AuthID WHERE
(tblVehicleJo bs.VehicleJobID =GetCurrentVehi cleJobID()) AND
(tblAuth.Auth CatID)<>2;

UNION SELECT [ProxyFName] & " " & [ProxyLName] AS Recipient FROM
tblVehicleJob s INNER JOIN tblProxies ON tblVehicleJobs. ProxyID =
tblProxies.Pr oxyID WHERE
tblVehicleJob s.VehicleJobID= GetCurrentVehic leJobID();