Union Query Used in Select Query Not Working

D

derekjansen

Hi All,

I am fairly certain this is a really basic question, but I'm having a
tough time figuring out how to make this work.

The first statement is a union query to include the master list of
employees with the "extra" list for employees who sometimes go by
nicknames or might have names that people frequently spell wrong:

(query name: allStaff)
SELECT [Employee Id] AS EmployeeID, [Person ID] AS PersonID, [First
Name] AS First, Initial, [Last Name] AS Last, [Work Email] AS Email,
[Department] AS Team, [Job title] AS Position, [Employee Status Type]
AS Status FROM staff
UNION
SELECT [EmployeeID], PersonID, First, Initial, Last, ExtraEmail AS
Email, Team, Position, Status FROM staffExtras
ORDER BY EmployeeID;

The second statement is a basic select query that uses the query above
called allStaff and information stored in a table called
cmFeedbackReferences. The purpose is to match up the people identified
as references from our candidates (the data in cmFeedbackReferences)
with the actual data in allStaff:

(query name: cmStaffFeedbackReference1)
SELECT cmFeedbackReferences.PersonID, cmFeedbackReferences.[Primary e-
mail address], cmFeedbackReferences.[Reference name 1],
cmFeedbackReferences.[Relation to applicant 1], cmFeedbackReferences.
[Reference e-mail 1], allStaff.EmployeeID
FROM cmFeedbackReferences LEFT JOIN allStaff ON cmFeedbackReferences.
[Primary e-mail address] = allStaff.Email
WHERE (((IIf(InStr(1,[Reference e-mail 1],"@teachforamerica",2)
0,"Yes","No"))="Yes"));

The problem is in second query, cmStaffFeedbackReference1 is not
showing the EmployeeID (or any other data from allStaff. Any ideas? Is
there something I'm doing here that just doesn't make sense? Thanks!

Derek
 
D

Douglas J. Steele

Your second query is doing a LEFT JOIN between cmFeedbackReferences and
allStaff, which means that it's going to have all the details from
cmFeedbackReferences whether or not there's a match between
cmFeedbackReferences.[Primary e-mail address] and allStaff.Email. If there
is no match, the allStaff fields will be Null in the resultant recordset.

Are you certain that there ARE matching e-mails between the two?

Incidentally, your WHERE clause could be simplified to

WHERE InStr(1,[Reference e-mail 1],"@teachforamerica",2) > 0


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi All,

I am fairly certain this is a really basic question, but I'm having a
tough time figuring out how to make this work.

The first statement is a union query to include the master list of
employees with the "extra" list for employees who sometimes go by
nicknames or might have names that people frequently spell wrong:

(query name: allStaff)
SELECT [Employee Id] AS EmployeeID, [Person ID] AS PersonID, [First
Name] AS First, Initial, [Last Name] AS Last, [Work Email] AS Email,
[Department] AS Team, [Job title] AS Position, [Employee Status Type]
AS Status FROM staff
UNION
SELECT [EmployeeID], PersonID, First, Initial, Last, ExtraEmail AS
Email, Team, Position, Status FROM staffExtras
ORDER BY EmployeeID;

The second statement is a basic select query that uses the query above
called allStaff and information stored in a table called
cmFeedbackReferences. The purpose is to match up the people identified
as references from our candidates (the data in cmFeedbackReferences)
with the actual data in allStaff:

(query name: cmStaffFeedbackReference1)
SELECT cmFeedbackReferences.PersonID, cmFeedbackReferences.[Primary e-
mail address], cmFeedbackReferences.[Reference name 1],
cmFeedbackReferences.[Relation to applicant 1], cmFeedbackReferences.
[Reference e-mail 1], allStaff.EmployeeID
FROM cmFeedbackReferences LEFT JOIN allStaff ON cmFeedbackReferences.
[Primary e-mail address] = allStaff.Email
WHERE (((IIf(InStr(1,[Reference e-mail 1],"@teachforamerica",2)
0,"Yes","No"))="Yes"));

The problem is in second query, cmStaffFeedbackReference1 is not
showing the EmployeeID (or any other data from allStaff. Any ideas? Is
there something I'm doing here that just doesn't make sense? Thanks!

Derek
 
D

Derek J

Hey Doug,

Thanks for the quick response! I'll have to go simplify that WHERE
clause. Unfortunately, the two tables do actually have matching e-
mails. Is there anything else about these SELECT statements and the
way I'm using them that might cause this problem? Thanks!

Derek

Your second query is doing a LEFT JOIN between cmFeedbackReferences and
allStaff, which means that it's going to have all the details from
cmFeedbackReferences whether or not there's a match between
cmFeedbackReferences.[Primary e-mail address] and allStaff.Email. If there
is no match, the allStaff fields will be Null in the resultant recordset.

Are you certain that there ARE matching e-mails between the two?

Incidentally, your WHERE clause could be simplified to

WHERE InStr(1,[Reference e-mail 1],"@teachforamerica",2) > 0

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)


I am fairly certain this is a really basic question, but I'm having a
tough time figuring out how to make this work.
The first statement is a union query to include the master list of
employees with the "extra" list for employees who sometimes go by
nicknames or might have names that people frequently spell wrong:
(query name: allStaff)
SELECT [Employee Id] AS EmployeeID, [Person ID] AS PersonID, [First
Name] AS First, Initial, [Last Name] AS Last, [Work Email] AS Email,
[Department] AS Team, [Job title] AS Position, [Employee Status Type]
AS Status FROM staff
UNION
SELECT [EmployeeID], PersonID, First, Initial, Last, ExtraEmail AS
Email, Team, Position, Status FROM staffExtras
ORDER BY EmployeeID;
The second statement is a basic select query that uses the query above
called allStaff and information stored in a table called
cmFeedbackReferences. The purpose is to match up the people identified
as references from our candidates (the data in cmFeedbackReferences)
with the actual data in allStaff:
(query name: cmStaffFeedbackReference1)
SELECT cmFeedbackReferences.PersonID, cmFeedbackReferences.[Primary e-
mail address], cmFeedbackReferences.[Reference name 1],
cmFeedbackReferences.[Relation to applicant 1], cmFeedbackReferences.
[Reference e-mail 1], allStaff.EmployeeID
FROM cmFeedbackReferences LEFT JOIN allStaff ON cmFeedbackReferences.
[Primary e-mail address] = allStaff.Email
WHERE (((IIf(InStr(1,[Reference e-mail 1],"@teachforamerica",2)
0,"Yes","No"))="Yes"));
The problem is in second query, cmStaffFeedbackReference1 is not
showing the EmployeeID (or any other data from allStaff. Any ideas? Is
there something I'm doing here that just doesn't make sense? Thanks!
 
D

Derek J

Doug, I wish I could hit delete on that last message - I was pulling
the wrong e-mail field and didn't even realize it. Primary e-mail is
from the candidate, not the staff member. It works now! Thanks for
your help, I'm sure I'll be back here with more questions - hopefully
next time better ones!

Derek

Hey Doug,

Thanks for the quick response! I'll have to go simplify that WHERE
clause. Unfortunately, the two tables do actually have matching e-
mails. Is there anything else about these SELECT statements and the
way I'm using them that might cause this problem? Thanks!

Derek

Your second query is doing a LEFT JOIN between cmFeedbackReferences and
allStaff, which means that it's going to have all the details from
cmFeedbackReferences whether or not there's a match between
cmFeedbackReferences.[Primary e-mail address] and allStaff.Email. If there
is no match, the allStaff fields will be Null in the resultant recordset.
Are you certain that there ARE matching e-mails between the two?
Incidentally, your WHERE clause could be simplified to
WHERE InStr(1,[Reference e-mail 1],"@teachforamerica",2) > 0
news:d406de8b-caf8-47d2-9c8e-02f875bd006a@a26g2000prf.googlegroups.com....
Hi All,
I am fairly certain this is a really basic question, but I'm having a
tough time figuring out how to make this work.
The first statement is a union query to include the master list of
employees with the "extra" list for employees who sometimes go by
nicknames or might have names that people frequently spell wrong:
(query name: allStaff)
SELECT [Employee Id] AS EmployeeID, [Person ID] AS PersonID, [First
Name] AS First, Initial, [Last Name] AS Last, [Work Email] AS Email,
[Department] AS Team, [Job title] AS Position, [Employee Status Type]
AS Status FROM staff
UNION
SELECT [EmployeeID], PersonID, First, Initial, Last, ExtraEmail AS
Email, Team, Position, Status FROM staffExtras
ORDER BY EmployeeID;
The second statement is a basic select query that uses the query above
called allStaff and information stored in a table called
cmFeedbackReferences. The purpose is to match up the people identified
as references from our candidates (the data in cmFeedbackReferences)
with the actual data in allStaff:
(query name: cmStaffFeedbackReference1)
SELECT cmFeedbackReferences.PersonID, cmFeedbackReferences.[Primary e-
mail address], cmFeedbackReferences.[Reference name 1],
cmFeedbackReferences.[Relation to applicant 1], cmFeedbackReferences.
[Reference e-mail 1], allStaff.EmployeeID
FROM cmFeedbackReferences LEFT JOIN allStaff ON cmFeedbackReferences.
[Primary e-mail address] = allStaff.Email
WHERE (((IIf(InStr(1,[Reference e-mail 1],"@teachforamerica",2)
0,"Yes","No"))="Yes"));
The problem is in second query, cmStaffFeedbackReference1 is not
showing the EmployeeID (or any other data from allStaff. Any ideas? Is
there something I'm doing here that just doesn't make sense? Thanks!
Derek
 
G

Gast

Hi All,

I am fairly certain this is a really basic question, but I'm having a
tough time figuring out how to make this work.

The first statement is a union query to include the master list of
employees with the "extra" list for employees who sometimes go by
nicknames or might have names that people frequently spell wrong:

(query name: allStaff)
SELECT [Employee Id] AS EmployeeID, [Person ID] AS PersonID, [First
Name] AS First, Initial, [Last Name] AS Last, [Work Email] AS Email,
[Department] AS Team, [Job title] AS Position, [Employee Status Type]
AS Status FROM staff
UNION
SELECT [EmployeeID], PersonID, First, Initial, Last, ExtraEmail AS
Email, Team, Position, Status FROM staffExtras
ORDER BY EmployeeID;

The second statement is a basic select query that uses the query above
called allStaff and information stored in a table called
cmFeedbackReferences. The purpose is to match up the people identified
as references from our candidates (the data in cmFeedbackReferences)
with the actual data in allStaff:

(query name: cmStaffFeedbackReference1)
SELECT cmFeedbackReferences.PersonID, cmFeedbackReferences.[Primary e-
mail address], cmFeedbackReferences.[Reference name 1],
cmFeedbackReferences.[Relation to applicant 1], cmFeedbackReferences.
[Reference e-mail 1], allStaff.EmployeeID
FROM cmFeedbackReferences LEFT JOIN allStaff ON cmFeedbackReferences.
[Primary e-mail address] = allStaff.Email
WHERE (((IIf(InStr(1,[Reference e-mail 1],"@teachforamerica",2)
0,"Yes","No"))="Yes"));

The problem is in second query, cmStaffFeedbackReference1 is not
showing the EmployeeID (or any other data from allStaff. Any ideas? Is
there something I'm doing here that just doesn't make sense? Thanks!

Derek
yesssssssssssssssssssssx
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

UNION query question 7
Union Query by choice.. 11
SUM in a UNION query 2
Duplicates in union query 3
Union Query 0
Union query 5
Subreport from union query 2
sorting in a normalizing union query 8

Top