Solved

SQL Question...

Posted on 2002-04-15
5
196 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 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 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month4 days, 17 hours left to enroll

635 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