Removing values from a drop-down list.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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---------------------------
 
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.
 
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...
 
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...
 
Back
Top