Column lookup getting in way

[JFoushee]

Hi... building a query to demonstrate orphaned/missing records.

In this case, all employees should be assigned an active position.

So I have a query that will show all employees who have bogus (missing) positions....

SELECT PersonnelID, Name, PositionID
FROM tblPersonnel
WHERE PositionID NOT IN (SELECT PositionID FROM tblPositions)
AND PositionID <> 0
ORDER BY PersonnelID

But I get back results....

_______________________________________________
| PersonnelID | Name            | Position     |
|-------------|-----------------|--------------|
|          60 | John Doe        |              |
|         231 | Jane Doe        |              |
|         286 | And so on...    |              |
|-------------|-----------------|--------------|

I explicitly asked for the PositionID, but because of a Lookup function behind the column, I get this goofy blank pulldown.

Is there a way to show the behind-the-scenes ID?

_______________________________________________
| PersonnelID | Name            | PositionID   |
|-------------|-----------------|--------------|
|          60 | John Doe        |           83 |
|         231 | Jane Doe        |          125 |
|         286 | And so on...    |          125 |
|-------------|-----------------|--------------|

 

[JFoushee]

Nevermind, I did this:

SELECT ... PositionID + 0

 

[lespaul]

looks like those are nulls....why not just:

SELECT PersonnelID, Name, PositionID
FROM tblPersonnel H
LEFT OUTER JOIN tblPosition P on H.PositionID = P.PositionID
WHERE H.PositionID <> 0
ORDER BY PersonnelID

check out Understanding SQL Joins

Leslie

Have you met Hardy Heron?