Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Question...

Posted on 2002-04-15
5
Medium Priority
?
197 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 2

Accepted Solution

by:
tppradeep18 earned 80 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 101

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

715 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