Solved

SQL Question...

Posted on 2002-04-15
5
190 Views
Last Modified: 2010-05-02
Hello,

I have a union query and need it have the combined items into a list box. I can only get the items from the SQL statement prior to the UNION part.

Sql = "SELECT EquipmentType FROM tbl_Equipment " _
    & "Where EquipmentType <> Null And Trim(EquipmentType) <> "" " _
    & "Union " _
    & "SELECT fld_EqType FROM tbl_Inventory_Equipment " _
    & "WHERE fld_EqType <> NULL and TRIM(fld_EqType) <> "" ;"
   
    g_rst.CursorLocation = adUseClient
    g_rst.Open sSqlx, g_conn, adOpenKeyset, adLockOptimistic
   
    Do While Not g_rst.EOF
        Me.cboEquipmentType.AddItem g_rst("EquipmentType").Value

        Me.cboEquipmentType.AddItem g_rst("fld_EqType").Value

        g_rst.MoveNext
    Loop

Also, using the example above, how can I union three tables?

Table3: Three_tbl
Field3: Three_fld

Thanks,

ADawn




0
Comment
Question by:ADawn
5 Comments
 
LVL 2

Accepted Solution

by:
tppradeep18 earned 20 total points
ID: 6943577
hi ADawn,

When a union query run, the recordset contains field names belonging to the querty in the first part of the union. The resultset will contain results from both the table. Suppose You have two tables Sisters and brothers. Sisters contain SName field and Brothers Contain BName field. Sister table has one record 'Alisha' and Brother table has one record 'Martin' (BOTh fields have same Data type). No run the following union querry

select SName from sisters union select BName from brothers

This will contain recordset as follows

SName
------

Alisha
Martin

As you see the recorset contains results from both table but the field name SNAme is from the first table sisters.


to UNION a third table use the followin query

select SName from sisters union select BName from brothers union select SBName from StepBrother



The syntax is as follows

{ < query specification > | ( < query expression > ) }
        UNION [ ALL ]
        < query specification | ( < query expression > )
            [ UNION [ ALL ] < query specification | ( < query expression > )
                [ ...n ] ]
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6944672
well said, tppradeep18.

it is important to repeat that the columns in all selects that are part of a union must match in number and in datatype exactly.  For example these would be invalid unions:

select sname from sisters
union
select age from brothers


select sname, dress_size from sisters
union
select bname from brothers

However, you can fake it:

select sname, dress_size from sisters
union
select bname, 0 from brothers

Last but not least, if you have to order the results, you have to do it after the last select statement in the union:


select sname, dress_size from sisters
union
select bname, pants_size from brother
order by sname, dress_size

or
order by 1,2   (if you want it to be less confusing

0
 
LVL 100

Expert Comment

by:mlmcc
ID: 6944889
listening
0
 

Author Comment

by:ADawn
ID: 6947740
With the help of mdougan it works. Thanks, ADawn
0
 

Author Comment

by:ADawn
ID: 6947741
With the help of mdougan it works. Thanks, ADawn
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now