Union Query Used in Select Query Not Working

  • Thread starter Thread starter derekjansen
  • Start date Start date
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"));
Click to expand...

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
 
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"));
Click to expand...

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
Click to expand...
 
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,
Click to expand...
I am fairly certain this is a really basic question, but I'm having a
tough time figuring out how to make this work.
Click to expand...
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:
Click to expand...
(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;
Click to expand...
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:
Click to expand...
(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"));
Click to expand...
Click to expand...
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!
Click to expand...
Derek
Click to expand...
Click to expand...
 
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.
Click to expand...
Are you certain that there ARE matching e-mails between the two?
Click to expand...
Incidentally, your WHERE clause could be simplified to
Click to expand...
WHERE InStr(1,[Reference e-mail 1],"@teachforamerica",2) > 0
Click to expand...
news:d406de8b-caf8-47d2-9c8e-02f875bd006a@a26g2000prf.googlegroups.com....
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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"));
Click to expand...

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
Click to expand...
yesssssssssssssssssssssx
 
Back
Top