The Eternal Newbie Strikes Back

[SaraMegan]

Hi, Everyone!

I was just asked a question by someone in HR about her database, and thought y'all might be able to help me figure it out...

There are two tables: Employees and Positions.

She wants to run a query that will show her all the vacant positions. Is there a way to check to see if a position number that appears in the Positions table also appears in the Employees table without having to type in the number?

Let me know if I need to be clearer...

Thanks!

--Sara

 

[glynch]

Try this:

SELECT Positions.PositionID, Positions.Position
FROM Employees RIGHT JOIN Positions ON Employees.PositionID = Positions.PositionID
WHERE (((Employees.PositionID) Is Null));

I used a positionID as the primary key of the Positions table, and included it as a foreign key in the Employees table, you can use the position number field in the same way if is works as a primary key for the Positions table.

 

[SaraMegan]

Thanks for your response!

I'm not sure it's what I'm looking for, however. I don't know much about SQL (I do all my stuff the point and click way...) but it looks like you're asking it to find a null value in the Employee table... The thing is, every employee has a position number, but not every position number has an employee... There are no null values for Position # (your PositionID) in the Employee table.

If I'm just totally whacked, please let me know...

--Sara

 

[glynch]

What it actually does is return the list of positions from the Positions table that have no corresponding value in the Employees table. Reading the SQL can be a little misleading if you aren't familiar with these types of queries.

You can open a new query in design view (if prompted, don't select any table). Hit the dropdown just underneath the File menu at the upper left of your screen, select SQL, then copy and paste the SQL statement I sent (change the field names as necessary), and then run it, you should see the results you are looking for.

 

[SaraMegan]

Thanks! I don't know what I was tweaking before, but it wasn't coming up with anything, but this time it worked! Thank you so much for your help.



--Sara