Union Query Used in Select Query Not Working

[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

 

[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

 

[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)

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

 

[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

 

[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