rinneii

New Member
Joined
Mar 20, 2019
Messages
18
I'm trying to list both Student and Faculty information using the Union operator. This is what I have so far:

SELECT FacNo, FacFirstName, FacLastName
From Faculty
UNION Select StdNo, stdFirstName, stdLastName
From Student;

When I ran it, all that displayed was the Faculty information. Should I be using Union All?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,982
Office Version
  1. 365
Platform
  1. Windows
Are their duplicates between the two tables?
Do the corresponding fields all have the same data types?
What do the FacNo and StdNo values look like?
 

rinneii

New Member
Joined
Mar 20, 2019
Messages
18
I didn't see any duplicates, and the fields have the same data types. FacNo and StdNo are short text with 9 characters (all numbers).
I'm not sure if this would be an issue, but the Student table was created in Access, and the Faculty table was made in Microsoft SQL Server Management Studio. They are linked and I used the ODBC database import.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,982
Office Version
  1. 365
Platform
  1. Windows
I cannot recreate the scenario.

What happens if you run each one individually, like this:

Query1:
Code:
[COLOR=#333333]SELECT FacNo, FacFirstName, FacLastName[/COLOR]
[COLOR=#333333]From Faculty;[/COLOR]

Query2:
Code:
[COLOR=#333333]Select StdNo, stdFirstName, stdLastName[/COLOR]
[COLOR=#333333]From Student;[/COLOR]
Do both queries return data?

If so, could you post small sample of each data table?
 

rinneii

New Member
Joined
Mar 20, 2019
Messages
18

ADVERTISEMENT

I'm sorry, but how do I post a sample? I read the 2 stickies at the top of the forum, but the link to download the add-in no longer works. Should I just copy the SQL from the Student and Faculty tables for you to try?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,982
Office Version
  1. 365
Platform
  1. Windows
From Access, you can just copy/paste from query/tables, like this:

IDMyDateStudentMyField
14/27/2018AdamMUS 5 - BULK - PAC
24/27/2018 4:00:00 PMBenMUS 5 - BULK - PAC
34/27/2018 6:00:00 PMCoryMUS 5 - BULK - PAC
44/28/2018BenMUS 5 - BULK - PAC
54/28/2018 9:00:00 AMBen
64/28/2018 11:11:00 AMDoug
74/29/2018Edie
84/29/2018 10:00:00 PMCory
95/5/2018Mora

<caption> Table1 </caption><thead>
</thead><tbody>
</tbody><tfoot></tfoot>

Just go into the table/query, click on the box in the upper left corner to select everything, and copy/paste.
If there is too much data in your table to do that, create a query to select a small subset of records and do that.
 

rinneii

New Member
Joined
Mar 20, 2019
Messages
18

ADVERTISEMENT

FacNoFacFirstNameFacLastNameFacDeptFacRankFacSalaryFacSupervisorFacHireDateFacZip
109876542JUDITHSMITHACCPROF86000
6/15/200849024
109876543JUDITHSMITHMGTPROF81000
6/30/200849024
210987654VICKIEMORRISMKTPROF85000
7/11/199949034
321098765JENNYNOLANMKTASST400002109876543/1/200149035
432109876JUDYWILLIAMSONFINASSO750007654321093/15/200249142
543210987VICTORIAEMMANUELBISPROF120000
4/15/199849111
654321098LEONARDFIBONBISASSO700005432109875/1/199649012
654422088SONGCARRIEBISASST1250005432109878/1/201749008
765432109NICKIMACONFINPROF65000
4/11/199949035
876543210CRISTOPHERCOLANMKTASST400002109876543/1/200149009
987654321JULIAMILLSFINASSO750007654321093/15/200249024

<caption>This is what the tables look like normally
Faculty
</caption><thead>
</thead><tbody>
</tbody><tfoot></tfoot>


StdNoStdFirstNameStdLastNameStdMajorStdClassStdGPAStdZip
123456789HOMERWELLSCISFR349008
124567890BOBNORBERTFINJR2.749035
234567890CANDYKENDALLACCJR3.549142
345678901WALLYKENDALLCISSR2.849003
456789012JOEESTRADAFINSR3.249001
567890123MARIAHDODGECISJR3.649024
678901234TESSDODGEACCSO3.349036
789012345ROBERTOMORALESFINJR2.549001
876543210CRISTOPHERCOLANCISSR449014
890123456LUKEBRAZZICISSR2.249006
901234566WILLIAMPILGRIMCISSO3.849035

<caption> Student </caption><thead>
</thead><tbody>
</tbody><tfoot></tfoot>


This was when I ran each of them separately (using the query)
FacNoFacFirstNameFacLastName
109876542JUDITHSMITH
109876543JUDITHSMITH
210987654VICKIEMORRIS
321098765JENNYNOLAN
432109876JUDYWILLIAMSON
543210987VICTORIAEMMANUEL
654321098LEONARDFIBON
654422088SONGCARRIE
765432109NICKIMACON
876543210CRISTOPHERCOLAN
987654321JULIAMILLS

<caption> Faculty </caption><thead>
</thead><tbody>
</tbody><tfoot></tfoot>

StdNostdFirstNamestdLastName
123456789HOMERWELLS
124567890BOBNORBERT
234567890CANDYKENDALL
345678901WALLYKENDALL
456789012JOEESTRADA
567890123MARIAHDODGE
678901234TESSDODGE
789012345ROBERTOMORALES
876543210CRISTOPHERCOLAN
890123456LUKEBRAZZI
901234566WILLIAMPILGRIM

<caption> Student </caption><thead>
</thead><tbody>
</tbody><tfoot></tfoot>
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,982
Office Version
  1. 365
Platform
  1. Windows
Can you confirm the data types of the following two fields: FacNo, StdNo
 

rinneii

New Member
Joined
Mar 20, 2019
Messages
18
FacNo is ShortText
StdNo is ShortText

(also thanks for the help on sharing table information!)
 

james_lankford

Well-known Member
Joined
Jan 11, 2009
Messages
1,210
try this

Code:
Select 
  stdLastName, 
  'student' as souce_table
From 
  Student


union 


SELECT 
  FacFirstName, 
  'faculty' as souce_table
From 
  Faculty
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,113
Messages
5,857,453
Members
431,880
Latest member
kar2rost

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top