SELECT/UNION query not working

E

EagleOne

2007

BTW, I did post this in "macros" which was probably not the best the group.

My file is Test.mdb which has 3 tables:

TransactionsTbl (This table has a field called AccountNumber)
TrialBOYTbl (This table has a field called AccountNumber)
TrialEOYTbl (This table has a field called AccountNumber)

I wish to add another Table (in Test.mdb) called AccountNumbers

My attempts have failed to write a successful SELECT UNION query which
I want only unique account numbers be added.

For sure, the answer is in my face but I cannot see it.

My Query is: (The error is runtime "error 3065 cannot execute a select query)

Currentdb.Execute "SELECT [AccountNumber] INTO AccountNumbers FROM TrialBOYTbl" & _
" UNION SELECT [AccountNumber] FROM TrialEOYTbl" & _
" UNION SELECT [AccountNumber] FROM TransactionsTbl" & _
" ORDER BY [AccountNumber];"

Any help appreciated.

TIA EagleOne
 
S

Sylvain Lafontaine

Without having tested it, probably that you'll have to transform the Select
part of your Select Into query into a subquery; someting like:

SELECT [AccountNumber] INTO AccountNumbers FROM
Select SQ.TrialBOYTbl From (
Select TrialBOYTbl
UNION SELECT [AccountNumber] FROM TrialEOYTbl
UNION SELECT [AccountNumber] FROM TransactionsTbl
ORDER BY [AccountNumber]
) as SQ;

Which might be rewritten by Access as:

SELECT [AccountNumber] INTO AccountNumbers FROM
Select SQ.TrialBOYTbl From [
Select TrialBOYTbl
UNION SELECT [AccountNumber] FROM TrialEOYTbl
UNION SELECT [AccountNumber] FROM TransactionsTbl
ORDER BY [AccountNumber]
]. as SQ;

Notice that because you are making an Insert Into, the ORDER BY is without
any real use here and can even be the source of yet another syntaxe error;
so you should drop it, too.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)
 
J

John W. Vinson

2007

BTW, I did post this in "macros" which was probably not the best the group.

My file is Test.mdb which has 3 tables:

TransactionsTbl (This table has a field called AccountNumber)
TrialBOYTbl (This table has a field called AccountNumber)
TrialEOYTbl (This table has a field called AccountNumber)

I wish to add another Table (in Test.mdb) called AccountNumbers

My attempts have failed to write a successful SELECT UNION query which
I want only unique account numbers be added.

For sure, the answer is in my face but I cannot see it.

My Query is: (The error is runtime "error 3065 cannot execute a select query)

Currentdb.Execute "SELECT [AccountNumber] INTO AccountNumbers FROM TrialBOYTbl" & _
" UNION SELECT [AccountNumber] FROM TrialEOYTbl" & _
" UNION SELECT [AccountNumber] FROM TransactionsTbl" & _
" ORDER BY [AccountNumber];"

Any help appreciated.

TIA EagleOne
Click to expand...

I don't think you can mix an action query into a union query in that way!
Instead, try using an Append query *based on a stored Union query*, or using a
Subquery:

INSERT INTO [AccountNumbers]
SELECT [AccountNumber] FROM
(SELECT AccountNumber FROM TrialBOYTbl
UNION
SELECT AccountNumber FROM TrialEOYTbl)

The Order By is pointless since the UNION will itself "de-duplicate" the
account numbers, and the resulting table has no defined order in any case.
 
E

EagleOne

Thanks for your time and knowledge.

I am not sure how to weave your code into SQL

Obviously, I have no formal SQL training.

That said,

I have created a Recordset successfuly with:

Set rst = currentdb.OpenRecordset("SELECT [AccountNumber] FROM TrialBOYTbl" & _
" UNION SELECT [AccountNumber] FROM TrialEOYTbl" & _
" UNION SELECT [AccountNumber] FROM TransactionsTbl" & _
" ORDER BY [AccountNumber];")

What SQL code can I use to get the recordset "rst" in a new table "AccountNumbers"?




John W. Vinson said:
2007

BTW, I did post this in "macros" which was proba TSTably not the best the group.

My file is Test.mdb which has 3 tables:

TransactionsTbl (This table has a field called AccountNumber)
TrialBOYTbl (This table has a field called AccountNumber)
TrialEOYTbl (This table has a field called AccountNumber)

I wish to add another Table (in Test.mdb) called AccountNumbers

My attempts have failed to write a successful SELECT UNION query which
I want only unique account numbers be added.

For sure, the answer is in my face but I cannot see it.

My Query is: (The error is runtime "error 3065 cannot execute a select query)

Currentdb.Execute "SELECT [AccountNumber] INTO AccountNumbers FROM TrialBOYTbl" & _
" UNION SELECT [AccountNumber] FROM TrialEOYTbl" & _
" UNION SELECT [AccountNumber] FROM TransactionsTbl" & _
" ORDER BY [AccountNumber];"

Any help appreciated.

TIA EagleOne
Click to expand...

I don't think you can mix an action query into a union query in that way!
Instead, try using an Append query *based on a stored Union query*, or using a
Subquery:

INSERT INTO [AccountNumbers]
SELECT [AccountNumber] FROM
(SELECT AccountNumber FROM TrialBOYTbl
UNION
SELECT AccountNumber FROM TrialEOYTbl)

The Order By is pointless since the UNION will itself "de-duplicate" the
account numbers, and the resulting table has no defined order in any case.
Click to expand...
 
S

Sylvain Lafontaine

Well, I shouldn't try to do two things at the same time. Here's the two
right versions; both with the []. syntaxe:

SELECT [AccountNumber] INTO AccountNumbers
FROM [
Select [AccountNumber] FROM TrialBOYTbl
UNION SELECT [AccountNumber] FROM TrialEOYTbl
UNION SELECT [AccountNumber] FROM TransactionsTbl
]. as SQ;

And:

INSERT INTO AccountNumbers ([AccountNumber])
Select SQ.AccountNumber From [
Select [AccountNumber] FROM TrialBOYTbl
UNION SELECT [AccountNumber] FROM TrialEOYTbl
UNION SELECT [AccountNumber] FROM TransactionsTbl
]. as SQ;

The first version is a Create New Table query: a new table will be created
or, if it already exists, its content will be deleted prior to add the new
records. The second case is the Append query: the new records will be added
to an existing table. Notice that in both cases, the subquery remains the
same.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Sylvain Lafontaine said:
Without having tested it, probably that you'll have to transform the
Select part of your Select Into query into a subquery; someting like:

SELECT [AccountNumber] INTO AccountNumbers FROM
Select SQ.TrialBOYTbl From (
Select TrialBOYTbl
UNION SELECT [AccountNumber] FROM TrialEOYTbl
UNION SELECT [AccountNumber] FROM TransactionsTbl
ORDER BY [AccountNumber]
) as SQ;

Which might be rewritten by Access as:

SELECT [AccountNumber] INTO AccountNumbers FROM
Select SQ.TrialBOYTbl From [
Select TrialBOYTbl
UNION SELECT [AccountNumber] FROM TrialEOYTbl
UNION SELECT [AccountNumber] FROM TransactionsTbl
ORDER BY [AccountNumber]
]. as SQ;

Notice that because you are making an Insert Into, the ORDER BY is without
any real use here and can even be the source of yet another syntaxe error;
so you should drop it, too.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


2007

BTW, I did post this in "macros" which was probably not the best the
group.

My file is Test.mdb which has 3 tables:

TransactionsTbl (This table has a field called AccountNumber)
TrialBOYTbl (This table has a field called AccountNumber)
TrialEOYTbl (This table has a field called AccountNumber)

I wish to add another Table (in Test.mdb) called AccountNumbers

My attempts have failed to write a successful SELECT UNION query which
I want only unique account numbers be added.

For sure, the answer is in my face but I cannot see it.

My Query is: (The error is runtime "error 3065 cannot execute a
select query)

Currentdb.Execute "SELECT [AccountNumber] INTO AccountNumbers FROM
TrialBOYTbl" & _
" UNION SELECT [AccountNumber] FROM TrialEOYTbl" & _
" UNION SELECT [AccountNumber] FROM TransactionsTbl" & _
" ORDER BY [AccountNumber];"

Any help appreciated.

TIA EagleOne
Click to expand...
Click to expand...
 
E

EagleOne

Have attempted the following and I still get an error:

Currentdb.execute "SELECT [AccountNumber] INTO AccountNumbers" _
& "FROM [Select [AccountNumber] FROM TrialBOYTbl" _
& " UNION SELECT [AccountNumber] FROM TrialEOYTbl" _
& " UNION SELECT [AccountNumber] FROM TransactionsTbl]. as SQ;

John mentioned that I should not use "Execute" with a "Select"

Thoughts?
 
E

EagleOne

Update:

Got it !!

strSQL = "SELECT [AccountNumber] INTO AccountNumbers FROM (Select [AccountNumber]" _
& " FROM TrialBOYTbl UNION SELECT [AccountNumber] FROM TrialEOYTbl" _
& " UNION SELECT [AccountNumber] FROM TransactionsTbl);"
DoCmd.RunSQL strSQL


Thanks for the help.
 
J

John W. Vinson

Update:

Got it !!

strSQL = "SELECT [AccountNumber] INTO AccountNumbers FROM (Select [AccountNumber]" _
& " FROM TrialBOYTbl UNION SELECT [AccountNumber] FROM TrialEOYTbl" _
& " UNION SELECT [AccountNumber] FROM TransactionsTbl);"
DoCmd.RunSQL strSQL
Click to expand...

That does somewhat resemble what I suggested... <g>

Glad you got it working.
 
S

Sylvain Lafontaine

You have a blank space missing between the end of the first line and the
beginning of the second one:

Currentdb.execute "SELECT [AccountNumber] INTO AccountNumbers" _
& "FROM [Select [AccountNumber] FROM TrialBOYTbl" _

should be:

Currentdb.execute "SELECT [AccountNumber] INTO AccountNumbers " _
& "FROM [Select [AccountNumber] FROM TrialBOYTbl" _

For the Execute command, you cannot use it with a Select query that returns
a resultset but in your case, there is no record returned as they are either
inserted or appended to a table. So you are using a Select statement but
it's not a Select query.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Have attempted the following and I still get an error:

Currentdb.execute "SELECT [AccountNumber] INTO AccountNumbers" _
& "FROM [Select [AccountNumber] FROM TrialBOYTbl" _
& " UNION SELECT [AccountNumber] FROM TrialEOYTbl" _
& " UNION SELECT [AccountNumber] FROM TransactionsTbl]. as SQ;

John mentioned that I should not use "Execute" with a "Select"

Thoughts?

Sylvain Lafontaine said:
Well, I shouldn't try to do two things at the same time. Here's the two
right versions; both with the []. syntaxe:

SELECT [AccountNumber] INTO AccountNumbers
FROM [
Select [AccountNumber] FROM TrialBOYTbl
UNION SELECT [AccountNumber] FROM TrialEOYTbl
UNION SELECT [AccountNumber] FROM TransactionsTbl
]. as SQ;

And:

INSERT INTO AccountNumbers ([AccountNumber])
Select SQ.AccountNumber From [
Select [AccountNumber] FROM TrialBOYTbl
UNION SELECT [AccountNumber] FROM TrialEOYTbl
UNION SELECT [AccountNumber] FROM TransactionsTbl
]. as SQ;

The first version is a Create New Table query: a new table will be created
or, if it already exists, its content will be deleted prior to add the new
records. The second case is the Append query: the new records will be
added
to an existing table. Notice that in both cases, the subquery remains the
same.
Click to expand...
Click to expand...
 
E

EagleOne

You are correct. I was being challenged (your teaching technique) to integrate the knowledge.
Originally, I kept attempting to Execute your code. I kept thinking about " cannot use an action
query into a union query" and how to code-correct action vs union.

Therefore I attempted DoCmd.RunSQL vs Execute. Once I got the contrast, I implemented your code.

I still have an open question. In my attempts to work around the error, I created a Recordset via
"OpenRecordSet." In a different situation, what SQL code would create a new table with the
Recordset as the the Tables contents?

Thanks John




John W. Vinson said:
Update:

Got it !!

strSQL = "SELECT [AccountNumber] INTO AccountNumbers FROM (Select [AccountNumber]" _
& " FROM TrialBOYTbl UNION SELECT [AccountNumber] FROM TrialEOYTbl" _
& " UNION SELECT [AccountNumber] FROM TransactionsTbl);"
DoCmd.RunSQL strSQL
Click to expand...

That does somewhat resemble what I suggested... <g>

Glad you got it working.
Click to expand...
 
E

EagleOne

Sylvain,

Thanks!

Note that you used a [ in front of FROM and ] after the TrialBOYTbl.

Later I changed the "[ and ]" to "( and )" and all worked well.

EagleOne
 
J

John W. Vinson

I still have an open question. In my attempts to work around the error, I created a Recordset via
"OpenRecordSet." In a different situation, what SQL code would create a new table with the
Recordset as the the Tables contents?
Click to expand...

Hrm. I don't know of any "off the shelf" way to do so. Typically a Recordset
is based on a SQL statement, so you could "cut out the middleman" by changing
that SQL statement into a MakeTable query.

In routine use, I almost NEVER have to programmatically create tables. 99+% of
the tables I use are designed into the database at the beginning; I've got a
couple of databases with 80 to 100 tables, and nary a make-table query to be
seen.
 
S

Sylvain Lafontaine

It's not a "]" after it, it's a "]." with the point (but without the
quotes). The query designer will often rewrite a subquery enclosed in
parenthesis by a subquery enclosed in []., so you must know both syntaxe.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Sylvain,

Thanks!

Note that you used a [ in front of FROM and ] after the TrialBOYTbl.

Later I changed the "[ and ]" to "( and )" and all worked well.

EagleOne

Sylvain Lafontaine said:
You have a blank space missing between the end of the first line and the
beginning of the second one:

Currentdb.execute "SELECT [AccountNumber] INTO AccountNumbers" _
& "FROM [Select [AccountNumber] FROM TrialBOYTbl" _

should be:

Currentdb.execute "SELECT [AccountNumber] INTO AccountNumbers " _
& "FROM [Select [AccountNumber] FROM TrialBOYTbl" _

For the Execute command, you cannot use it with a Select query that
returns
a resultset but in your case, there is no record returned as they are
either
inserted or appended to a table. So you are using a Select statement but
it's not a Select query.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with SELECT UNION 7
Syntax error 3075 in VBA/SQL involving two LEFT JOINS 5
Sum error on select query 1
SELECT DISTINCT query from three sources possible? 11
Union Query and Field Alias 7
double entries for each record when running a query 4
Union Query 0
Union All Query 2

Top