Help with SELECT UNION

[EagleOne]

2007

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

 

[Jeff Boyce]

What happens if you use SELECT DISTINCT...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Douglas J. Steele]

As the error message is telling you, you cannot use the Execute method with
Select statements. The Execute method is strictly for Action queries
(Update, Delete, Insert Into)

What are you hoping to achieve by executing a Select statement?

 

[Steve Schapel]

Eagle,

The easiest way to get this right is to make your Union Query first, with
the 3 existing tables. Save it. Then use this query as the basis of your
Make-Table Query.

 

[Steve Schapel]

Eagle,

The easiest way to get this right is to make your Union Query first, with
the 3 existing tables. Save it. Then use this query as the basis of your
Make-Table Query.

 

[Steve Schapel]

Eagle,

The easiest way to get this right is to make your Union Query first, with
the 3 existing tables. Save it. Then use this query as the basis of your
Make-Table Query.

 

[Steve Schapel]

Eagle,

The easiest way to get this right is to make your Union Query first, with
the 3 existing tables. Save it. Then use this query as the basis of your
Make-Table Query.

 

[Steve Schapel]

Eagle,

The easiest way to get this right is to make your Union Query first, with
the 3 existing tables. Save it. Then use this query as the basis of your
Make-Table Query.