[thelist] Question about Access database programming with ASP 3.0

Thu Jun 10 10:07:21 CDT 2004

Wouldn't this work?

SELECT 
	Names.Name
,	Houses.House
,	Phones.Phone
FROM Names
INNER JOIN Phones ON Names.NameID=Phones.PhoneID
INNER JOIN Houses ON Names.HouseID=Houses.HouseID
GROUP BY Names.NameID, Names.HouseID

...unless you actually want to eliminate redundant appearances of Names
and Houses in your recordset, in which case you should look into
DataShaping (search for it on msdn website).

Jonathan wrote:
> Howdy everyone...
> 
> Listen, I have a simple problem I'm trying to solve for a friend of
> mine, but I'm not an ASP programmer.  I've been fighting with this
> one for hours...
> 
> I've got this database in Access that looks like this:
> http://www.firebright.com/staged/db.gif
> 
> What I'm looking to generate is something that looks about like this:
> 
> NAME		HOME					PHONE
> --------------------------------------------------------------------
> Jim		House	based on houseID		884-343-3333
> 							884-343-3332
> 							884-343-3335
> 
> James		House based on houseID		884-343-3311
> 							884-343-3331
> 
> 
> I'm getting close to what I want, but it's not right...  This is the
> code I have written...
> 
> <%
> Set MyConn = Server.CreateObject("ADODB.Connection")
> MdbFilePath = Server.MapPath("baza.mdb")
> MyConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" &
> MdbFilePath & ";"
> SQL_query = "SELECT DISTINCT Names.NameID, Names.HouseID, Names.Name,
> Phones.PhoneID, Phones.Phone From Names, Phones WHERE Names.NameID =
> Phones.NameID"
> Set RS = MyConn.Execute(SQL_query)
> %>
> 
> But it's outputting something more like:
> 
> Name House  Phone
> Jim 3 215-243-1240
> Jim 3 267-210-7237
> Jim 3 206-333-0575
> James 1 916-339-3163
> James 1 916-265-0320
> James 1 916-639-0325
> James 1 916-339-0161
> 
> Now that's not right...
> 
> This is the full source code:
> 
> <HTML>
> <BODY>
> <%
> Set MyConn = Server.CreateObject("ADODB.Connection")
> MdbFilePath = Server.MapPath("baza.mdb")
> MyConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" &
> MdbFilePath & ";"
> SQL_query = "SELECT DISTINCT Names.NameID, Names.HouseID, Names.Name,
> Phones.PhoneID, Phones.Phone From Names, Phones WHERE Names.NameID =
> Phones.NameID"
> 'SQL_query = "SELECT DISTINCT Houses.House, Houses.HouseID,
> Names.NameID, Names.Name From Names, Houses, Phones ORDER BY
> Names.NameID" 'SQL_query = "SELECT NameID,Name FROM Names, Houses
> WHERE NameID" 
> Set RS = MyConn.Execute(SQL_query)
> %>
> 
> <table>
> <tr>
>  <th>Name</th>
>  <th>House </th>
>  <th>Phone</th>
>  <th></th>
>  <th></th>
>  <th></th>
> </tr>
> 
> <%
> WHILE NOT RS.EOF
> %>
> <!--
> SELECT Phones.PhoneID
> FROM Phones
> INNER JOIN
> 
> SELECT Houses.HouseID, Phones.PhoneD FROM Names, Houses, Phones WHERE
> Names.NameID=Phones.NameID AND Names.NameID=Houses.HouseID
>  -->
> <tr>
>  <td><a href="?name=<%=RS("NameID")%>"><%=RS("Name")%></a></td>
>  <td><a href="?house=<%=RS("HouseID")%>"><%=RS("HouseID")%></a></td>
>  <td>
>  <%
>  Response.Write RS("Phone")
>  %>
> 
>  </td>
>  <td></td>
>  <td></td>
>  <td></td>
> </tr>
> 
> <%
> RS.MoveNext
> WEND
>  Response.Write "PageCount : " & RS.PageCount & "<br>" & vbcrlf
> %>
> 
> </table>
> </BODY>
> </HTML>
> 
> How do I solve this problem?  You'll note it's a file access Access
> database, and unfortunately it has to stay that way.
> 
> Any help would be appreciated.
> 
> Jonathan