MS Access 97 / UNION / SELECT INTO

[marco]

Dear List,

as it seems, MS SQL as used in Access does not allow a select INTO
within a UNION query. Also, it seems that a UNION query can not be used
as a subquery.

Maybe my (simplified) problem can avoid these technicalities: the
original table has columns

A1, A2, B1, B2, C1, C2.

Now, the sum over entries in A1 is taken if the corresponding entries
in A2 satisfy some condition. The same is done for the B- and C-
columns. All those sums should be one single sum, as if the table would
be

F1, F2,

where F2 contains each and all entry/ies from A1, B1, and C1, and F2
those from A2, B2, and C2, and the sum is just taken over those values.

My current solution is like the following UNION-query, with results
saved (by hand) into tbl2.

(SELECT A1 FROM tbl WHERE A2="take me")
UNION ALL
(SELECT B1 FROM tbl WHERE B2="take me")
UNION ALL
(SELECT C1 FROM tbl WHERE C2="take me");

Then, the actual sums are taken by
SELECT SUM(F1) FROM tbl2;

Question 1:
Can the first query use a "SELECT INTO" clause, and if so, where?

Question 2a:
Can the second query use the first one - without the hand-saved
intermediate result?

Question 2b:
If the second query references several fields from the first query,
does Access recompute that first query each time? (A test-query with
ten references has needed about ten times as long.)

Question 3:
Can the two queries be combined into one? Which way is most efficient?
Thanks very much in advance for any advice you can give!

Marco

PS
The data table is quite large, so time IS an issue here. The UNION
query takes about 1.5hrs, and a query referencing the UNION-query about
ten times takes about 15 hours.

[Lyle Fairfield]

I think these are about what you are asking. One is for JEt 4.0 the other
for JEt 3.5.Perhaps, if you study them some of your questions will be
answered.

Sub temp40()
Dim sql As String
Dim rst As DAO.Recordset
sql = "SELECT A1 AS F1 FROM tbl WHERE A2 = 'Take Me'"
sql = sql & vbNewLine & "UNION ALL"
sql = sql & vbNewLine & "SELECT B1 AS F1 FROM tbl WHERE B2 = 'Take
Me'"
sql = "SELECT SUM(Subquery.F1) AS SumF1 FROM" & vbNewLine & "(" & sql
& " ) AS Subquery"
Set rst = DBEngine(0)(0).OpenRecordset(sql)
Debug.Print "-----------"
Debug.Print "4.0"
Debug.Print sql
If Not rst.BOF Then
Debug.Print rst.Fields("SumF1").Value
Else
Debug.Print "No records"
End If
Debug.Print "-----------"
Set rst = Nothing
End Sub

Sub temp35()
Dim sql As String
Dim rst As DAO.Recordset
sql = "SELECT A1 AS F1 FROM tbl WHERE A2 = 'Take Me'"
sql = sql & vbNewLine & "UNION ALL"
sql = sql & vbNewLine & "SELECT B1 AS F1 FROM tbl WHERE B2 = 'Take
Me'"
sql = "SELECT SUM(Subquery.F1) AS SumF1 FROM" & vbNewLine & "[" & sql
& " ]. AS Subquery"
Set rst = DBEngine(0)(0).OpenRecordset(sql)
Debug.Print "-----------"
Debug.Print "3.5"
Debug.Print sql
If Not rst.BOF Then
Debug.Print rst.Fields("SumF1").Value
Else
Debug.Print "No records"
End If
Debug.Print "-----------"
Set rst = Nothing
End Sub

--
Lyle Fairfield

[marco]

Dear Lyle,

thanks a ton for your detailed message. It took me a few days until I
could respond, sorry.

I used the query itself, not VBA, but it works perfectly. Looks like
the []. syntax was the catch. In fact, this query avoids the ten-fold
call to the subquery, and is just as fast as the two queries by hand,
with the advantage of doing everything automatically.

Very awesome, and thanks a bunch again!

Marco Loskamp