Removing values from a drop-down list.

J

James

I have the following sql and would like a list in a drop-down to only display
the positions that have not already been selected. If a positionId in
tblSchedule is on 9/16/05 shift 2 then don't show that position in the list.
I hope I explaned it enough. Does anyone know how to do this?

-----------------sql---------------------
SELECT tblPosition.PositionID, tblPosition.PositionName,
tblPosition.ShiftID, FROM tblPosition LEFT JOIN tblSchedule ON
tblPosition.PositionID = tblSchedule.PositionID
GROUP BY tblPosition.PositionID, tblPosition.PositionName,
tblPosition.ShiftID,
HAVING (((tblPosition.ShiftID)=[Forms].[frmSchedule].[cboShiftID]))
ORDER BY tblPosition.PositionName;
----------------------end sql---------------------------
 
M

[MVP] S.Clark

Typically, to find values in one table that are NOT in another, you can use
an Outer Join and the criteria where {PrimaryFieldKeyID} Is Null.

I see that you have the outer join, but think you would need
WHERE tblSchedule.PositionID is Null

as this would be those tblPosition.PositionID's that are not found in the
Schedule table.
 
J

James

Thank you for your help. I added the WHERE clause. Now we are close. It
shows only the positions that have not been assigned to any record, but I
would like it to be record specific. So, if record1 has position1 assiged
record 2 will still show position1, but record1 will not. Again thanks!

[MVP] S.Clark said:
Typically, to find values in one table that are NOT in another, you can use
an Outer Join and the criteria where {PrimaryFieldKeyID} Is Null.

I see that you have the outer join, but think you would need
WHERE tblSchedule.PositionID is Null

as this would be those tblPosition.PositionID's that are not found in the
Schedule table.


--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting


James said:
I have the following sql and would like a list in a drop-down to only
display
the positions that have not already been selected. If a positionId in
tblSchedule is on 9/16/05 shift 2 then don't show that position in the
list.
I hope I explaned it enough. Does anyone know how to do this?

-----------------sql---------------------
SELECT tblPosition.PositionID, tblPosition.PositionName,
tblPosition.ShiftID, FROM tblPosition LEFT JOIN tblSchedule ON
tblPosition.PositionID = tblSchedule.PositionID
GROUP BY tblPosition.PositionID, tblPosition.PositionName,
tblPosition.ShiftID,
HAVING (((tblPosition.ShiftID)=[Forms].[frmSchedule].[cboShiftID]))
ORDER BY tblPosition.PositionName;
----------------------end sql---------------------------
Click to expand...
Click to expand...
 
V

Vincent Johns

James said:
Thank you for your help. I added the WHERE clause. Now we are close. It
shows only the positions that have not been assigned to any record, but I
would like it to be record specific. So, if record1 has position1 assiged
record 2 will still show position1, but record1 will not. Again thanks!
Click to expand...

This isn't very clear to me. What do you want it to do? Is
[tblPosition].[PositionID] one of your [position1] or [record 2] values?
You have specifically asked for [tblPosition].[PositionID] values with
no corresponding records in [tblSchedule], so you won't be able to, for
example, sort them according to some field in [tblSchedule].

:

Typically, to find values in one table that are NOT in another, you can use
an Outer Join and the criteria where {PrimaryFieldKeyID} Is Null.

I see that you have the outer join, but think you would need
WHERE tblSchedule.PositionID is Null

as this would be those tblPosition.PositionID's that are not found in the
Schedule table.


--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting


I have the following sql and would like a list in a drop-down to only
display
the positions that have not already been selected. If a positionId in
tblSchedule is on 9/16/05 shift 2 then don't show that position in the
list.
I hope I explaned it enough. Does anyone know how to do this?

-----------------sql---------------------
SELECT tblPosition.PositionID, tblPosition.PositionName,
tblPosition.ShiftID, FROM tblPosition LEFT JOIN tblSchedule ON
tblPosition.PositionID = tblSchedule.PositionID
GROUP BY tblPosition.PositionID, tblPosition.PositionName,
tblPosition.ShiftID,
HAVING (((tblPosition.ShiftID)=[Forms].[frmSchedule].[cboShiftID]))
ORDER BY tblPosition.PositionName;
----------------------end sql---------------------------
Click to expand...
Click to expand...
Click to expand...
 
Top