How to create a union table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to select fields from two tables and Union them into a table, but
it seems I cannot do a 'select into ...', but I can create a recordset of the
Union. Is there a way to do this other than to create a recordset and save
the recordset to a table?

Thanks in advance,

WayneM
 
Try a set of 2 Queries:

1. The Union Query to select the required rows.

2. A Make-Table Query using the Union Query as the DataSource.
 
Van T. Dinh said:
Try a set of 2 Queries:

1. The Union Query to select the required rows.

2. A Make-Table Query using the Union Query as the DataSource.
Click to expand...
Van,

I tried to do a union with an 'into tblx' but I get a message that an Action
query cannot be used as a row source. I can do this in SQL server. Any
suggestion on the sql syntax?

Thanks,

Wayne
 
I am not sure what you described. Union Query is not an Action Query.

Post relevant Source Table details and the TWO SQL Strings you tried.
 
Not sure whether this work or not but you can try:

SELECT F1, F2
INTO [tblUnion]
FROM
(
SELECT F1, F2 FROM tblA
UNION
SELECT F4, F4 FROM tblB
)
 
Van T. Dinh said:
Not sure whether this work or not but you can try:

SELECT F1, F2
INTO [tblUnion]
FROM
(
SELECT F1, F2 FROM tblA
UNION
SELECT F4, F4 FROM tblB
)

--
HTH
Van T. Dinh
MVP (Access)



WayneM said:
OK. I understand how to do this with two separate queries, using one as the
datasource for the make table query. But I asked the wrong question to
begin. I am doing this in VB6 where I am using DAO and everything is in sql.
Is there some way to do this in just sql?

Thanks,

WayneM
Click to expand...
Click to expand...
Van,

I tried your suggestion, but that does not work. It was the first thing I
tried, since this what you would expect to work in sql. Any other
suggestions? How about some type of 'insert into' ?

Thanks for any other ideas. I suppose I could just create a table and then
just do inserts from the union recordset. Ugh. This will be slow.

Thanks,

Wayne
 
Hi Wayne,

PMFBI

This works in Access.

SELECT q.F1, q.F2
INTO [tblUnion]
FROM
(
SELECT F1, F2 FROM tblA
UNION
SELECT F3, F4 FROM tblB
) As q;

or this works

SELECT F1, F2
INTO [tblUnion]
FROM
(
SELECT F1, F2 FROM tblA
UNION
SELECT F3, F4 FROM tblB
) As q;

or this works (as Van suggested)

SELECT F1, F2
INTO [tblUnion]
FROM
(
SELECT F1, F2 FROM tblA
UNION
SELECT F3, F4 FROM tblB
) ;

Do you see how these are different from what
you "already tried?"

select TblA.F1, TblA.F2 into UnionTbl from TblA Union Select TblB.F3,
TblB.F4 from TblB

Good luck,

Gary Walter

WayneM said:
Van T. Dinh said:
Not sure whether this work or not but you can try:

SELECT F1, F2
INTO [tblUnion]
FROM
(
SELECT F1, F2 FROM tblA
UNION
SELECT F4, F4 FROM tblB
)
Click to expand...
I tried your suggestion, but that does not work. It was the first thing I
tried, since this what you would expect to work in sql. Any other
suggestions? How about some type of 'insert into' ?

Thanks for any other ideas. I suppose I could just create a table and then
just do inserts from the union recordset. Ugh. This will be slow.

Thanks,

Wayne
Click to expand...
 
Gary Walter said:
Hi Wayne,

PMFBI

This works in Access.

SELECT q.F1, q.F2
INTO [tblUnion]
FROM
(
SELECT F1, F2 FROM tblA
UNION
SELECT F3, F4 FROM tblB
) As q;

or this works

SELECT F1, F2
INTO [tblUnion]
FROM
(
SELECT F1, F2 FROM tblA
UNION
SELECT F3, F4 FROM tblB
) As q;

or this works (as Van suggested)

SELECT F1, F2
INTO [tblUnion]
FROM
(
SELECT F1, F2 FROM tblA
UNION
SELECT F3, F4 FROM tblB
) ;

Do you see how these are different from what
you "already tried?"

select TblA.F1, TblA.F2 into UnionTbl from TblA Union Select TblB.F3,
TblB.F4 from TblB

Good luck,

Gary Walter

WayneM said:
Van T. Dinh said:
Not sure whether this work or not but you can try:

SELECT F1, F2
INTO [tblUnion]
FROM
(
SELECT F1, F2 FROM tblA
UNION
SELECT F4, F4 FROM tblB
)
Click to expand...
I tried your suggestion, but that does not work. It was the first thing I
tried, since this what you would expect to work in sql. Any other
suggestions? How about some type of 'insert into' ?

Thanks for any other ideas. I suppose I could just create a table and then
just do inserts from the union recordset. Ugh. This will be slow.

Thanks,

Wayne
Click to expand...
Click to expand...
OK, "UNCLE". I give up. I created two tables called tblA and tblB with
fields F1, F2 and F3, F4 (text) and added 2 rows of data. Then I cut and
pasted your syntax into the SQL view of the query builder and got the same
message I always got, which is "Syntax Error on From Clause" with the cursor
highlighting the second Select keyword.

The Union part of the query works fine, but the combination is where the
problem is.

I do believe you, but I must be missing something here. I cannot believe
this is so difficult. Am I this stupid?

Thanks,

Wayne
 
Hi Wayne,

Possibly your situation is not "changing"
the query to the following "form"

SELECT q.F1, q.F2
INTO tblUnion
FROM
[SELECT F1, F2 FROM tblA
UNION
SELECT F3, F4 FROM tblB]. AS q;

I'm sorry I did not mention this...
Access will take earlier "forms"
and automatically put brackets
and ending period around the
"virtual union table."

Apologies,

Gary Walter

WayneM said:
Gary Walter said:
Hi Wayne,

PMFBI

This works in Access.

SELECT q.F1, q.F2
INTO [tblUnion]
FROM
(
SELECT F1, F2 FROM tblA
UNION
SELECT F3, F4 FROM tblB
) As q;

or this works

SELECT F1, F2
INTO [tblUnion]
FROM
(
SELECT F1, F2 FROM tblA
UNION
SELECT F3, F4 FROM tblB
) As q;

or this works (as Van suggested)

SELECT F1, F2
INTO [tblUnion]
FROM
(
SELECT F1, F2 FROM tblA
UNION
SELECT F3, F4 FROM tblB
) ;

Do you see how these are different from what
you "already tried?"

select TblA.F1, TblA.F2 into UnionTbl from TblA Union Select TblB.F3,
TblB.F4 from TblB

Good luck,

Gary Walter

WayneM said:
:

Not sure whether this work or not but you can try:

SELECT F1, F2
INTO [tblUnion]
FROM
(
SELECT F1, F2 FROM tblA
UNION
SELECT F4, F4 FROM tblB
)

--
HTH
Van T. Dinh
MVP (Access)

I tried your suggestion, but that does not work. It was the first thing I
tried, since this what you would expect to work in sql. Any other
suggestions? How about some type of 'insert into' ?

Thanks for any other ideas. I suppose I could just create a table and then
just do inserts from the union recordset. Ugh. This will be slow.

Thanks,

Wayne
Click to expand...
Click to expand...
OK, "UNCLE". I give up. I created two tables called tblA and tblB with
fields F1, F2 and F3, F4 (text) and added 2 rows of data. Then I cut and
pasted your syntax into the SQL view of the query builder and got the same
message I always got, which is "Syntax Error on From Clause" with the cursor
highlighting the second Select keyword.

The Union part of the query works fine, but the combination is where the
problem is.

I do believe you, but I must be missing something here. I cannot believe
this is so difficult. Am I this stupid?

Thanks,

Wayne
Click to expand...
 
I hope you noted that the SQL String you posted previously was different
from what I posted.

OTOH, like I posted previously, I would have done it differently in Access.
 
Hi Wayne,

One other thing that may be
different for your situation....

this brackets/period "virtual table"
will not allow any other brackets
within the construction...
i.e., you cannot use this if your table names
contain spaces -- meaning you would have to
surround the table name(s) with brackets....
this will cause this "SQL construction" to fail.

In addition to Van's optimable suggestion,
if saving the union query is not possible,
I would not have a problem (in Access)
saving the union table (with indexes on fields that
will keep out duplicates from both tables),

then, in DAO code,

Dim db As DAO.Database

Set db = CurrentDb()

db.Execute "DELETE * FROM tblUnion", dbFailOnError
db.Execute "INSERT INTO tblUnion (f1,f2) SELECT f1, f2 FROM tblA",dbFailOnError
db.Execute "INSERT INTO tblUnion (f1,f2) SELECT f3, f4 FROM tblB",dbFailOnError

db.Close
Set db = Nothing

In Access at least, it is preferrable to run update
queries as opposed to make table queries.

Good luck,

Gary Walter

Gary Walter said:
Possibly your situation is not "changing"
the query to the following "form"

SELECT q.F1, q.F2
INTO tblUnion
FROM
[SELECT F1, F2 FROM tblA
UNION
SELECT F3, F4 FROM tblB]. AS q;

I'm sorry I did not mention this...
Access will take earlier "forms"
and automatically put brackets
and ending period around the
"virtual union table."

Apologies,

Gary Walter

WayneM said:
Gary Walter said:
Hi Wayne,

PMFBI

This works in Access.

SELECT q.F1, q.F2
INTO [tblUnion]
FROM
(
SELECT F1, F2 FROM tblA
UNION
SELECT F3, F4 FROM tblB
) As q;

or this works

SELECT F1, F2
INTO [tblUnion]
FROM
(
SELECT F1, F2 FROM tblA
UNION
SELECT F3, F4 FROM tblB
) As q;

or this works (as Van suggested)

SELECT F1, F2
INTO [tblUnion]
FROM
(
SELECT F1, F2 FROM tblA
UNION
SELECT F3, F4 FROM tblB
) ;

Do you see how these are different from what
you "already tried?"

select TblA.F1, TblA.F2 into UnionTbl from TblA Union Select TblB.F3,
TblB.F4 from TblB

Good luck,

Gary Walter

:

:

Not sure whether this work or not but you can try:

SELECT F1, F2
INTO [tblUnion]
FROM
(
SELECT F1, F2 FROM tblA
UNION
SELECT F4, F4 FROM tblB
)

--
HTH
Van T. Dinh
MVP (Access)

I tried your suggestion, but that does not work. It was the first thing I
tried, since this what you would expect to work in sql. Any other
suggestions? How about some type of 'insert into' ?

Thanks for any other ideas. I suppose I could just create a table and then
just do inserts from the union recordset. Ugh. This will be slow.

Thanks,

Wayne
Click to expand...
OK, "UNCLE". I give up. I created two tables called tblA and tblB with
fields F1, F2 and F3, F4 (text) and added 2 rows of data. Then I cut and
pasted your syntax into the SQL view of the query builder and got the same
message I always got, which is "Syntax Error on From Clause" with the cursor
highlighting the second Select keyword.

The Union part of the query works fine, but the combination is where the
problem is.

I do believe you, but I must be missing something here. I cannot believe
this is so difficult. Am I this stupid?

Thanks,

Wayne
Click to expand...
Click to expand...
 
Finally a solution!. Thanks Gary. It seems the trick is putting it into the
right "form". I guess you have to have the brackets/period. This was not
either of your or Van's earlier suggestion, which is why I could not make
this work.

I don't know why it has to be this way, but so be it. I'll just use what
works.

Thanks,

WayneM
 
Back
Top