473,558 Members | 2,923 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

MS Access 97 / UNION / SELECT INTO

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.

Nov 25 '05 #1
2 9750
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(s ql)
Debug.Print "-----------"
Debug.Print "4.0"
Debug.Print sql
If Not rst.BOF Then
Debug.Print rst.Fields("Sum F1").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(s ql)
Debug.Print "-----------"
Debug.Print "3.5"
Debug.Print sql
If Not rst.BOF Then
Debug.Print rst.Fields("Sum F1").Value
Else
Debug.Print "No records"
End If
Debug.Print "-----------"
Set rst = Nothing
End Sub

--
Lyle Fairfield
Nov 25 '05 #2
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

Nov 28 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
3939
by: s_gregory | last post by:
The mdb is considerable size 70 +- mb. A complex union query was working well, but when an additional union select... was added into the query, selecting identical fields from a different source, it began to have problems. Failure was observed only in a few PC's at first. For example, in an NT 4.0 SP6 PC, it continued to work OK. But in...
4
1679
by: Kent Eilers | last post by:
The following query changes when I save it: ================================================================== SELECT AcctID FROM (SELECT as AcctID FROM tblOrderHeader UNION
1
4183
by: matthew kramer | last post by:
Hi, I'm using the reverse pivot technique on an excel spreadsheet to create a list dataset from which I can make a pivot table in excell. The only problem is that because there are so many years going across, whenever I try to do this the computer tells me it was only able to do an incomplete list because the full list exceeds 65,000...
3
1925
by: Dan | last post by:
I hate it when people think that their own misunderstandings are bugs in the program, but this time I think I've got something. If I run the following SQL code in Access 2000, I get unexpected results: SELECT MY_FIELD FROM ( SELECT NULL AS MY_FIELD FROM DUAL
6
4586
by: das | last post by:
Hello all, I have a table with thousands of rows and is in this format: id col1 col2 col3 col4 --- ------ ----- ------ ------ 1 nm 78 xyz pir 2 bn 45 abc dir I now want to get the data from this table in this format:
10
2083
by: Robert | last post by:
I am an attorney in a non-profit organization and a self-taught programmer. I'm trying to create a client db that will allow me to search for potential conflicts of interest based either on Social Security # or on Last Name. I've created two different tables with the following fields in each table: ClientInfo Client# (primary key) First...
4
52412
MMcCarthy
by: MMcCarthy | last post by:
To view Access queries in SQL rather than Access query design - open the query design window and change the view to SQL: Select Statement SELECT FROM ; Append Statement INSERT INTO (, , ) VALUES ('value1', #value2#, value3); This assumes value1 is a string, value2 is a date and value 3 is some other datatype
2
2190
by: jafastinger | last post by:
I have a large union. If I break it into its individual parts they all run quick. The longest is the last select it takes 2 minutes to fetch all rows. When I run the query below it does not come back for quite some time. 20 minutes. There are very few duplicates. When I run the sql's individually the first two give no warning but the...
27
13764
by: MLH | last post by:
How can I turn the following into a make-table query? SELECT & " " & AS Recipient FROM tblVehicleJobs INNER JOIN tblAddnlOwnrs ON tblVehicleJobs.VehicleJobID = tblAddnlOwnrs.VehicleJobID WHERE tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID(); UNION SELECT AS Recipient FROM tblVehicleJobs INNER JOIN tblLienHolders ON...
0
7629
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8061
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7593
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7914
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5455
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5172
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3602
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1164
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
869
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.