Union Query

[Guest]

Running Access 2003 ---

I want to take the last names from a table called "Committee Members" and
the last names from a table called "Faculty&Staff" and put them together in
one list.

I tried the following SQL, which returned only the names from the "Committee
Members" table.

SELECT [CMLName]
FROM [Committee Members]

UNION SELECT [Last Name]
FROM [Faculty&Staff]



What did I do wrong?
--
Thanks,
Bruce Hartsell
Department of Social Work
California State University
Bakersfield, CA

 

[Tom Ellison]

Dear Bruce:

When you use UNION the query will eliminate all duplicates. Perhaps there
are no new last names found in Faculty&Staff.

If you use UNION ALL you will get all rows. Also, it may be faster.

I recommend you identify the source of all rows. From this you can see
better what is happening:

SELECT [CMLName], "Committee" AS Source
FROM [Committee Members]
UNION ALL
SELECT [Last Name], "F & S" AS Source
FROM [Faculty&Staff]

Tom Elison

 

[John Vinson]

Running Access 2003 ---

I want to take the last names from a table called "Committee Members" and
the last names from a table called "Faculty&Staff" and put them together in
one list.

I tried the following SQL, which returned only the names from the "Committee
Members" table.

SELECT [CMLName]
FROM [Committee Members]

UNION SELECT [Last Name]
FROM [Faculty&Staff]



What did I do wrong?

Nothing obvious. What do you see if you run the last SELECT as a
standalone query?

One possibility: special characters such as & are often problems in
queries. Access allows them... but sometimes misinterprets them. Try
renaming the table FacultyStaff and see if that helps.

John W. Vinson[MVP]

 

[Guest]

That helped me figure out my error.
--
Thanks,
Bruce Hartsell
Department of Social Work
California State University
Bakersfield, CA

Dear Bruce:

When you use UNION the query will eliminate all duplicates. Perhaps there
are no new last names found in Faculty&Staff.

If you use UNION ALL you will get all rows. Also, it may be faster.

I recommend you identify the source of all rows. From this you can see
better what is happening:

SELECT [CMLName], "Committee" AS Source
FROM [Committee Members]
UNION ALL
SELECT [Last Name], "F & S" AS Source
FROM [Faculty&Staff]

Tom Elison

Running Access 2003 ---

I want to take the last names from a table called "Committee Members" and
the last names from a table called "Faculty&Staff" and put them together
in
one list.

I tried the following SQL, which returned only the names from the
"Committee
Members" table.

SELECT [CMLName]
FROM [Committee Members]

UNION SELECT [Last Name]
FROM [Faculty&Staff]



What did I do wrong?
--
Thanks,
Bruce Hartsell
Department of Social Work
California State University
Bakersfield, CA