I'm looking for some feedback and advice from anyone willing to help with the problem below.

I've been handed a phone directory database for use on our Intranet. We need to be able to add, edit, and print directly from the Intranet Administration areas. The problem I believe is going to be with the database design.

Currently, it's set up like so:

Column names:
department | subdepartment | last | first | phone...
With entries like so:
sheriff patrol doe john 999-9999
sheriff booking smith james 999-9999
sheriff records jones davey 999-9999

On viewable pages, the plan is to have a user select a department from a drop down menu and on a foloowing page, display the first subdepartment (if one's present), then display all users within that subdepartment, then move on to the next subdepartment and so on..

I.E.:
The "Sheriff" employees can be found at:
--Patrol
  John Doe - 999-9999

--Booking
  James Smith - 999-9999

--Records
  Davey Jones - 999-9999

If I loop through the records, it returns the above layout 3 seperate times. I know why it's doing this, but not how to combat the problem. Should I have the database reformetted to better accomodate my needs, or is there a way to single out the data I need as shown above. If the databse could be better setup, I'm completely open to anyone's suggestions!

Chris

Can you please post your code?

This the current code that I'm testing with.

<%
  Dim objCommand, objRS, objCommand1, objRS1, strDept
  strDept = "Data Processing"

  Set objCommand = Server.CreateObject("ADODB.Command")
  objCommand.ActiveConnection = strConnect
  objCommand.CommandText = "SELECT subdepartment FROM directory WHERE department = '" & strDept & "'"
  objCommand.CommandType = adCmdText
  Set objRS = objCommand.Execute
  Set objCommand = Nothing

  If objRS.EOF Then
    Response.Write "<table width=600 border=0><tr>" & _
                   "<td class=text align=center><b>Your search for " & strDept & " produced" & _
                   " no results</b></td></tr>" & _
                   "<tr><td bgcolor=cccccc></td></tr>" & _
                   "<tr><td bgcolor=eeeeee class=text align=center><a href='index.asp'>Search Again</a> | " & _
                   "<a href='index.asp'>Return To Main Menu</a></td></tr>" & _
                   "</table>"
  Else
    Response.Write "<table width=450 align=center border=0>"
  Do While Not objRS.EOF
    Response.Write "<tr><td align=left class=text bgcolor=cccccc>" & UCase(objRS("subdepartment")) & "</td></tr>"

  Set objCommand1 = Server.CreateObject("ADODB.Command")
  objCommand1.ActiveConnection = strConnect
  objCommand1.CommandText = "SELECT * FROM directory WHERE subdepartment = '" & objRS("subdepartment") & "' ORDER BY last"
  objCommand1.CommandType = adCmdText
  Set objRS1 = objCommand1.Execute
  Set objCommand1 = Nothing

  While Not objRS1.EOF
    Response.Write "<tr><td align=left class=text>" & objRS1("first") & " " & objRS1("last") & "</td></tr>"
  objRS1.MoveNext
  WEND
  objRS.MoveNext
  Loop
  Response.Write "<tr valign=top><td bgcolor=333333></td></tr>" & _
                 "<tr><td class=text align=center bgcolor=eeeeee><a href='departsearch.asp'>Search Again</a>" & _
                 " | <a href='index.asp'>Return To Directory Home</a></td></tr>" & _
                 "</table>"

  End If

  objRS1.Close
  Set objRS1 = Nothing

  objRS.Close
  Set objRS = Nothing
%>

I believe I have solved my own problem. I added another table to the database as a department descriptor table that contains the department name with it's subdepartments. In the very first query, I specify the new table and my results are only one set of subdepartments. The inner loop fires off correctly and displays my multiple users.

Here's the new test code.

<%
  Dim objCommand, objRS, objCommand1, objRS1, strDept
  strDept = "Data Processing"

  Set objCommand = Server.CreateObject("ADODB.Command")
  objCommand.ActiveConnection = strConnect
  objCommand.CommandText = "SELECT subdepartment FROM fddepartments1 WHERE department = '" & strDept & "' ORDER BY subdepartment"
  objCommand.CommandType = adCmdText
  Set objRS = objCommand.Execute
  Set objCommand = Nothing

  If objRS.EOF Then
    Response.Write "<table width=600 border=0><tr>" & _
                   "<td class=text align=center><b>Your search for " & strDept & " produced" & _
                   " no results</b></td></tr>" & _
                   "<tr><td bgcolor=cccccc></td></tr>" & _
                   "<tr><td bgcolor=eeeeee class=text align=center><a href='index.asp'>Search Again</a> | " & _
                   "<a href='index.asp'>Return To Main Menu</a></td></tr>" & _
                   "</table>"
  Else
    Response.Write "<table width=450 align=center border=0>"
  Do While Not objRS.EOF
    Response.Write "<tr><td align=left class=text bgcolor=cccccc>" & UCase(objRS("subdepartment")) & "</td></tr>"

  Set objCommand1 = Server.CreateObject("ADODB.Command")
  objCommand1.ActiveConnection = strConnect
  objCommand1.CommandText = "SELECT * FROM directory WHERE subdepartment = '" & objRS("subdepartment") & "'"
  objCommand1.CommandType = adCmdText
  Set objRS1 = objCommand1.Execute
  Set objCommand1 = Nothing

  While Not objRS1.EOF
    Response.Write "<tr><td align=left class=text>" & objRS1("first") & " " & objRS1("last") & "</td></tr>"
  objRS1.MoveNext
  WEND
  objRS.MoveNext
  Loop
  Response.Write "<tr valign=top><td bgcolor=333333></td></tr>" & _
                 "<tr><td class=text align=center bgcolor=eeeeee><a href='departsearch.asp'>Search Again</a>" & _
                 " | <a href='index.asp'>Return To Directory Home</a></td></tr>" & _
                 "</table>"

  End If

  objRS1.Close
  Set objRS1 = Nothing

  objRS.Close
  Set objRS = Nothing
%>