rinneii

New Member
Joined
Mar 20, 2019
Messages
16
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?
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,488
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
16
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
54,488
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
16

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
54,488
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
16

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
54,488
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
16
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,172
try this

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


union 


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

Watch MrExcel Video

Forum statistics

Threads
1,109,360
Messages
5,528,237
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top