on information in another table.



Basically, I have a staff records table with a one-2-many relationship to 

a bookings table in ACCESS.



I currently display the information from the staff table but would like to 

display only staff that have no booking records for a particular day.



Any suggestions as to the best method for this.



Current code below:



 Dim objRS, strShift

  

  If Request("Shift") = "4" Then

	If Request("Day") = "MonShift" Then

    strShift = "SELECT * FROM StaffDetails WHERE MonNight= -1 AND 

Status='Available'"

    ElseIf Request("Day") = "TueShift" Then

    strShift = "SELECT * FROM StaffDetails WHERE TueNight= -1 AND 

Status='Available'"

    ElseIf Request("Day") = "WedShift" Then

    strShift = "SELECT * FROM StaffDetails WHERE WedNight= -1 AND 

Status='Available'"

    ElseIf Request("Day") = "ThuShift" Then

    strShift = "SELECT * FROM StaffDetails WHERE ThuNight= -1 AND 

Status='Available'"

    ElseIf Request("Day") = "FriShift" Then

    strShift = "SELECT * FROM StaffDetails WHERE FriNight= -1 AND 

Status='Available'"

    ElseIf Request("Day") = "SatShift" Then

    strShift = "SELECT * FROM StaffDetails WHERE SatNight= -1 AND 

Status='Available'"

    ElseIf Request("Day") = "SunShift" Then

    strShift = "SELECT * FROM StaffDetails WHERE SunNight= -1 AND 

Status='Available'"

    End If

  Else

  If Request("Day") = "MonShift" Then

    strShift = "SELECT * FROM StaffDetails WHERE Status='Available' AND 

MonShift= " & Request("Shift")

    If Request("Shift") = "1" OR "2" Then

	strShift= strShift & " OR MonShift= 3"

	End If

	ElseIf Request("Day") = "TueShift" Then

    strShift = "SELECT * FROM StaffDetails WHERE Status='Available' AND 

TueShift= " & Request("Shift")

    If Request("Shift") = "1" OR "2" Then

	strShift= strShift & " OR TueShift= 3"

	End If

    ElseIf Request("Day") = "WedShift" Then

    strShift = "SELECT * FROM StaffDetails WHERE Status='Available' AND 

WedShift= " & Request("Shift")

    If Request("Shift") = "1" OR "2" Then

	strShift= strShift & " OR WedShift= 3"

	End If

    ElseIf Request("Day") = "ThuShift" Then

    strShift = "SELECT * FROM StaffDetails WHERE Status='Available' AND 

ThuShift= " & Request("Shift")

    If Request("Shift") = "1" OR "2" Then

	strShift= strShift & " OR ThuShift= 3"

	End If

    ElseIf Request("Day") = "FriShift" Then

    strShift = "SELECT * FROM StaffDetails WHERE Status='Available' AND 

FriShift= " & Request("Shift")

    If Request("Shift") = "1" OR "2" Then

	strShift= strShift & " OR FriShift= 3"

	End If

    ElseIf Request("Day") = "SatShift" Then

    strShift = "SELECT * FROM StaffDetails WHERE Status='Available' AND 

SatShift= " & Request("Shift")

    If Request("Shift") = "1" OR "2" Then

	strShift= strShift & " OR SatShift= 3"

	End If

    ElseIf Request("Day") = "SunShift" Then

    strShift = "SELECT * FROM StaffDetails WHERE Status='Available' AND 

SunShift= " & Request("Shift")

    If Request("Shift") = "1" OR "2" Then

	strShift= strShift & " OR SunShift= 3"

	End If

    End If

   End If

   

   Set objRS = Server.CreateObject("ADODB.Recordset")

   objRS.Filter = "Region= '" & Session("Region") & "'"

   objRS.Open strShift, objConn1

    

  If Not objRS.EOF Then

    Response.Write _

      "<TABLE BORDER=""1"" CELLSPACING=""3"" CELLPADDING=""3"">" & _

      "  <TR>" & _

      "    <TH>StaffID" 

               If Session("PersonID") <> "" Then 

                 Response.Write "<BR><FONT SIZE=""-1"">Click on StaffID of 

your preffered staff to continue</FONT>"

               End If 

           Response.Write "</TH>" & _

      "    <TH>KnownAs</TH>" & _

      "	   <TH>Surname</TH>" & _

      "	   <TH>Region</TH>" & _

      "    <TH>Gender</TH>" & _

      "	   <TH>Can Sleep In?</TH>" & _

	  "  </TR>"

	  Do While Not objRS.EOF

      Response.Write "<TR ALIGN=CENTER>"

      If Session("PersonID") <> "" Then

        Response.Write _ 

          "<TD><A HREF=""CheckStaff2.asp?StaffID=" & objRS("StaffID")

&"&KnownAs=" & objRS("KnownAs")&"&Surname=" & objRS("Surname")&""">" & _ 

          objRS("StaffID") & "</A></TD>"

      Else       

        Response.Write "<TD>" & objRS("StaffID") & "</TD>"

      End If

      Response.Write _ 

        "<TD>" & objRS("KnownAs") & "</TD>" & _ 

        "<TD>" & objRS("Surname") & "</TD>" & _

        "<TD>" & objRS("Region") & "</TD>" & _

        "<TD>" & objRS("Gender") & "</TD>"

        

		If Request("Day") = "MonShift" Then

		Response.Write "<TD>" & Disp(objRS("MonSleep")) & "</TD>"

		ElseIf Request("Day") = "TueShift" Then

		Response.Write "<TD>" & Disp(objRS("TueSleep")) & "</TD>"

		ElseIf Request("Day") = "WedShift" Then

		Response.Write "<TD>" & Disp(objRS("WedSleep")) & "</TD>"

		ElseIf Request("Day") = "ThuShift" Then

		Response.Write "<TD>" & Disp(objRS("ThuSleep")) & "</TD>"

		ElseIf Request("Day") = "FriShift" Then

		Response.Write "<TD>" & Disp(objRS("FriSleep")) & "</TD>"

		ElseIf Request("Day") = "SatShift" Then

		Response.Write "<TD>" & Disp(objRS("SatSleep")) & "</TD>"

		ElseIf Request("Day") = "SunShift" Then

		Response.Write "<TD>" & Disp(objRS("SunSleep")) & "</TD>"

		End If

		Response.Write "</TR>"

      objRS.MoveNext

    Loop			    

    Response.Write "</TABLE>"

    objRS.close

    Set objRS = Nothing

  Else  

    Response.Write "<CENTER><H2>No Staff Available for your selection. Go 

back and try again</H2></CENTER>"

  End If