Query to update table from a Select query (1 Viewer)

sumdumgai

Registered User.
Local time
Today, 09:59
Joined
Jul 19, 2007
Messages
453
I have a query that correctly 'Unions' unique values for two fields from 6 tables. How can I insert those two unique fields into another table? This is what I've tried which gives me an error (action query cannot be used as row source):
Code:
SELECT F1, F2 into myTb  FROM
    (SELECT F1, F2 FROM T1
    UNION
    SELECT F1, F2 FROM T2 asderived
    UNION 
    SELECT F1, F2 FROM T3 asderived
    UNION 
    SELECT F1, F2 FROM T4 as derived
    UNION 
    SELECT F1, F2 FROM T5 as derived)
    UNION SELECT F1, F2 FROM T6


What am I doing wrong? Thanks.
 

June7

AWF VIP
Local time
Today, 05:59
Joined
Mar 9, 2014
Messages
5,423
SELECT INTO creates a table, not updates or inserts existing.

Remove the as derived from each SELECT (missing a space in 2 of them).

Also have closing paren in wrong place. And must end the SQL with a semi-colon.

UNION will not allow duplicate data. Might want to construct a field that identifies the source.

SELECT F1, F2 INTO myTb FROM
(SELECT F1, F2, "T1" AS Src FROM T1
UNION
SELECT F1, F2, "T2" FROM T2
UNION
SELECT F1, F2, "T3" FROM T3
UNION
SELECT F1, F2, "T4" FROM T4
UNION
SELECT F1, F2, "T5" FROM T5
UNION
SELECT F1, F2, "T6" FROM T6);
 

sumdumgai

Registered User.
Local time
Today, 09:59
Joined
Jul 19, 2007
Messages
453
Getting the same error: An action query cannot be used as a row source.
 

June7

AWF VIP
Local time
Today, 05:59
Joined
Mar 9, 2014
Messages
5,423
Okay, need to use an alias name for the subquery.

SELECT F1, F2 INTO myTb FROM
(SELECT F1, F2, "T1" AS Src FROM T1
UNION
SELECT F1, F2, "T2" FROM T2
UNION
SELECT F1, F2, "T3" FROM T3
UNION
SELECT F1, F2, "T4" FROM T4
UNION
SELECT F1, F2, "T5" FROM T5
UNION
SELECT F1, F2, "T6" FROM T6) AS Derived;

If you don't want duplicate pairs then remove the Src field. If you want ALL records even if duplicates from each table then use UNION ALL.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:59
Joined
Feb 28, 2001
Messages
26,999
Did you correct the original query's syntax error?

Code:
SELECT F1, F2 into myTb  FROM
    (SELECT F1, F2 FROM T1
    UNION
    SELECT F1, F2 FROM T2 asderived
    UNION 
    SELECT F1, F2 FROM T3 asderived
    UNION 
    SELECT F1, F2 FROM T4 as derived
    UNION 
    SELECT F1, F2 FROM T5 as derived[COLOR="Red"])[/COLOR]
    UNION SELECT F1, F2 FROM T6

That red right-parenthesis attempts to UNION a SELECT with a SELECT-INTO, and that won't work. When you attempted the alternative methods offered to you, did you correct that parenthesis error?
 

sumdumgai

Registered User.
Local time
Today, 09:59
Joined
Jul 19, 2007
Messages
453
This is what I have:
Code:
SELECT F1, F2 into myTb FROM
    (SELECT F1, F2, "T1" FROM T1
    UNION
    SELECT F1, F2, "T2" FROM T2
    UNION 
    SELECT F1, F2, "T3" FROM T3
    UNION 
    SELECT F1, F2, "T4" FROM T4
    UNION 
    SELECT F1, F2, "T5" FROM T5
    UNION 
    SELECT F1, F2, "T6" FROM T6) as derived;


Works but I'm getting duplicate pairs.
 

sumdumgai

Registered User.
Local time
Today, 09:59
Joined
Jul 19, 2007
Messages
453
These two queries appear to work together to give me what I need:


Code:
'Q Union 6

SELECT F1, F2 FROM
    (SELECT F1, F2 FROM T1
    UNION
    SELECT F1, F2 FROM T2
    UNION 
    SELECT F1, F2 FROM T3
    UNION 
    SELECT F1, F2 FROM T4
    UNION 
    SELECT F1, F2 FROM T5)
    UNION SELECT F1, F2 FROM T6;


and

Code:
INSERT INTO [myTb]
SELECT [Q Union 6].*
FROM [Q Union 6];


Thanks for your help.
 

June7

AWF VIP
Local time
Today, 05:59
Joined
Mar 9, 2014
Messages
5,423
I described in post 4 how to eliminate or include duplicate pairs.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:59
Joined
Feb 28, 2001
Messages
26,999
Well, first I don't see any need for the "as derived" because all you were doing was providing a local alias that you didn't then use for anything. So I'm glad to see you got rid of it.

Second, strictly from set theory considerations, you have two queries:

1. A query that UNIONs five tables in a sub-query and then UNIONs the result with a sixth table in the outer query. (in post 6)

2. A query that UNIONS six tables in one statement. (in post 7)

Because of the rules of set theory, the two queries should produce ABSOLUTELY IDENTICAL sets. That is because (a) there is an equivalent in set theory to the math concepts of associativity and commutativity, and (b) you have no exclusionary clauses in any of the queries.

You are adding together six sets of records and the ONLY difference is the syntax of the statements by which they are formed. Therefore, if you look at the records from the query outputs of method #1 and of method #2, the SAME RECORDS would be presented.

If you say they are not the same then you are not testing correctly OR there is an error that you are suppressing so you don't realize that there is a difference somewhere. Do you have error notification disabled when you run that beast? Because there is NO SEMANTIC DIFFERENCE between the two methods.

Oh, the records might or might not appear in the same order if you displayed the resultant recordset. BUT ... if you have paid attention in this forum, you know that without an ordering clause, you cannot rely on the order in which you will see things even for a single table. That's because set theory imposes no order.

A query is theoretically a "gang-bang" event in which everything happens at the same time. Of course we know that isn't true, mechanically speaking - but the point is that Access strictly adheres to the idea that when it is done doing what you asked it to do, you can't tell anything about the way it did things. Not only Access, by the way. ANY DB engine that complies with ANSI standard SQL will follow the same general rules. (That's why they call it a standard.)

From your post #6:

Works but I'm getting duplicate pairs.

That can only happen if there are duplicates in your dataset. A basic rule in designing a database is that Access won't tell you anything you didn't tell it first. But the other side of that coin is that if you ask for everything it has, Access will give it to you whether what you gave it was unique or duplicated.
 

June7

AWF VIP
Local time
Today, 05:59
Joined
Mar 9, 2014
Messages
5,423
Well, I tested with and without the alias name. Only worked with.
The one SQL in post 4 worked so why run 2 queries?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:59
Joined
Feb 28, 2001
Messages
26,999
Code:
SELECT F1, F2 INTO myTb FROM
(SELECT F1, F2, "T1" AS Src FROM T1
UNION
SELECT F1, F2, "T2" FROM T2
UNION
SELECT F1, F2, "T3" FROM T3
UNION
SELECT F1, F2, "T4" FROM T4
UNION
SELECT F1, F2, "T5" FROM T5
UNION
SELECT F1, F2, "T6" FROM T6) AS Derived;

Again, the question is WHY you have the AS clauses (alias clauses) when you don't use them?

This query might make more sense this way:

Code:
SELECT F1, F2, [COLOR="Red"]Src[/COLOR] INTO myTb FROM
(SELECT F1, F2, "T1" [COLOR="Red"]AS Src [/COLOR]FROM T1
UNION
SELECT F1, F2, "T2" FROM T2
UNION
SELECT F1, F2, "T3" FROM T3
UNION
SELECT F1, F2, "T4" FROM T4
UNION
SELECT F1, F2, "T5" FROM T5
UNION
SELECT F1, F2, "T6" FROM T6) [COLOR="Blue"]AS Derived;[/COLOR]

The parts in RED go together and would make a query that produces something useful. The part in BLUE totally escapes me. The ALIAS in that case is never used, but the thing about aliases is, they only exist while the query is open. So no other query can refer to a query called "Derived" unless it uses the query as a basis. BUT, the outer query is a SELECT INTO sequence that DOES NOT RETURN A RECORDSET so cannot be referenced externally anyway.

And now that I look more closely, sumdumgai, your query was not actually returning duplicates. You had removed the only distinguishing feature from that SELECT-INTO sequence so didn't retain the thing that would have revealed the source of that APPARENT duplication.

If you have a sales rep who sells 1 of the same product in each of two months, but don't include the month identifier, you will get the same apparent record twice. BUT it isn't the same record. You just dropped the thing that made it unique, so you didn't realize it was a REAL duplication.
 

sumdumgai

Registered User.
Local time
Today, 09:59
Joined
Jul 19, 2007
Messages
453
Sorry folks, but I've been away. Like I said, my post #7 seems like it's working. The 6 tables have an F1 and an F2 field. In each of the 6 tables, there are duplicate F1's and duplicate F2's but never a duplicate pairing. I want my result table to have all of the unique pairs from these 6 tables. So, if T1 has F1=1 and F2=1, and T2 has F1=1 and F2=1, then my result table will have only one pairing of F1=1 and F2=1.


With the small test data set I have, it looks like post #7 is working for me. Shouldn't it?
Thanks.
 

June7

AWF VIP
Local time
Today, 05:59
Joined
Mar 9, 2014
Messages
5,423
Having the calculated Src field can provide an identifier so the row will not be duplicate of row in other tables. Yes, the Src field could be included in the outer query - my oversight not showing that. The AS Derived alias was required for the SELECT INTO to work. It failed without.
 

sumdumgai

Registered User.
Local time
Today, 09:59
Joined
Jul 19, 2007
Messages
453
Src field can provide an identifier so the row will not be duplicate of row in other tables
Are you addressing this to me? Do I need the Src field even though #7 seems to be working?
Similarly, do I need th 'AS derived' even though #7 seems to be working?
Thanks.
 

June7

AWF VIP
Local time
Today, 05:59
Joined
Mar 9, 2014
Messages
5,423
I was responding to The_Doc_Man.

Go with whatever works. Just pointing out that what I provided also works and it is one query. See post 4. As stated, remove the Src field if you don't want duplicate pairs.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:59
Joined
Feb 28, 2001
Messages
26,999
June7, I won't argue the point. I simply don't see the value of the "AS Derived" in the example. The alias name is never referenced. If it is not referenced, then I don't know why it would be needed. You say it works with it and fails without it. But what, then, is the nature of the failure?

sumdumgai, the comments about the SRC field are because you complained about having duplicates. It is my belief that you really don't have duplicate records on the input side. What you have is camouflaged OUTPUT records - because you stripped off the things that would have told you that they were NOT duplicates. You lost the identities of the source tables.
 

June7

AWF VIP
Local time
Today, 05:59
Joined
Mar 9, 2014
Messages
5,423
Without AS Derived alias, it gave error "action query cannot be used as row source".

Picture a SELECT INTO FROM without the nested subquery. There would be a table/query name following the FROM. Just having the nested query was not enough. The action still needed to see a table name.

Including the SRC was a suggestion to allow duplicate pairs and identify source table. If OP just wants unique pairs regardless of source, then don't include the calculated SRC field. UNION will not allow duplicate records - UNION ALL will.
 
Last edited:

Users who are viewing this thread

Top Bottom