CODEWITHSUNDEEP
sqlvbams-accessms-access-2007union
skevthedev
skevthedev

Reputation: 465

UNION query: received expected results in unexpected format

so this is my first time working with unions and I am trying to query 3 tables.

Abstract

I am trying to select 3 fields from 3 different tables (a field per table) that have the same id.

I expected each set of the record to have the results from the 3 fields and then I could use the 3 results. What seems to be happening is that it takes all the results and creates a recordset for each

Any guidance would be greatly appreciated, for all i know, I could be going about this the completely wrong way.

Here is the code I am using

Dim rs1 As DAO.Recordset
Dim unionquery As String

    unionquery = "select 
                    [Test Script] from TEST_SCRIPTS where TEST_ID= " & Me.TEST_ID & " 
                  union select 
                    PROC_ID from PROC_CHECK where TEST_ID= " & Me.TEST_ID  & " 
                  union select all 
                    [Software Version] from SOFTWARE_VERSION where TEST_ID= " &  
                    Me.TEST_ID & " "

    Set rs1 = CurrentDb.OpenRecordset(unionquery, dbOpenForwardOnly)

    If Not (rs1.EOF And rs1.BOF) Then

        Do Until rs1.EOF = True

        ' I THOUGHT THIS WOULD WORK BUT I GET AN ERROR SAYING rs1!PROC_ID AND
        ' rs1![Software Version] are not part of the recordset

        ' WHEN I ALERT OUT rs1![Test Script] i get the whole set
        MsgBox " " & rs1![Test Script] & ", " & rs1!PROC_ID & ", " 
                   & rs1![Software Version] & " "

        Loop

   End If

   rs1.Close
   Set rs1 = Nothing

Upvotes: 0

Views: 121

Answers (2)

4dmonster
4dmonster

Reputation: 3031

The "UNION" clause append rows not columns. So resulting recordset has only one column named [Test Script] populated with filtered values from 

a: TEST_SCRIPTS where TEST_ID= " & Me.TEST_ID
b: PROC_CHECK where TEST_ID= " & Me.TEST_ID
c: SOFTWARE_VERSION where TEST_ID= " &  Me.TEST_ID

and you probably want such query

select [Test Script] , [PROC_ID] , [Software Version] FROM (TEST_SCRIPTS INNER JOIN 
PROC_CHECK ON TEST_SCRIPTS.TEST_ID = PROC_CHECK.TEST_ID) INNER JOIN AS SOFTWRE_VERSION     ON 
TEST_SCRIPTS.TEST_ID = SOFTWRE_VERSION.TEST_ID;

so you can get (if TEST_ID is unique in tables) one row and access fields as you typed in question rs1![Test Script] & ", " & rs1!PROC_ID & ", " & rs1![Software Version] & " "

Upvotes: 1

HansUp
HansUp

Reputation: 97101

Add this to your code just before the OpenRecordset line:

Debug.Print unionquery

Then you can run the code, go to the Immediate window (Ctrl+g), and copy the text of the union query. Then create a new query in the query designer, switch to SQL View, paste in the text, and run the query.

That effort will allow you to see the fields returned by the query ... you will immediately notice that fields named PROC_ID and [Software Version] are not included in that recordset. I think you will see a single field named [Test Script], but check for yourself.

Your first order of business must be to revise the query so that it returns the information you want. Once you have that query working in the query designer, revise your VBA code to use that query's SQL.

Upvotes: 1

Related Questions