468,107 Members | 1,445 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,107 developers. It's quick & easy.

How to show multiple records in the same line

On this page, you'll see that some PubID's are repeated several times:
http://www.ihea.info/pages/website/test1.asp

That's because some records have more than one attribute. For example,
Title with pubID 10 is about both Cervical Cancer and Endometrial
Cancer, so pubID 10 appears twice.

How do I make ASP show me only one instance of record 10 and then
simply concantenate Cervical Cancer and Endometrial Cancer?

So it would look like this:

10 North Africa Swahili Cervical Cancer: Stay
Healthy! Cervical Cancer & Endometrial Cancer

Here's my relationship:
http://www.ihea.info/pages/website/testimages/rel.gif
tblDiseaseTitle serves as an intermediary to enable many-to-many
relationship between tblDisease (where the multiple subjects come
from) and tblTitle.

If it is easier to correct this in database, I use Access 2002.

thanks in advance,

crispy

Jul 19 '05 #1
5 2051
First, you have to group the records by the PubID (easy, using Sort)

Second, use a loop to figure out "breaks" in the pubID

' Initialize previous ID to empty
prevPubID = vbNullstring

' Loop through the entire recordset
Do While Not Rs.EOF
' Set the current pub ID
curPubID = Rs("PubID")

' If the current ID not equal to previous one, create a new paragraph
If curPubID <> prevPubID Then
Response.Write("<P>" & curPubID)
End If

' Write out the attributes
Response.Write(Rs("Title"))

Rs.MoveNext

' Set previous ID to current one
prevPubID = curPubID
Loop

--
Manohar Kamath
Editor, .netWire
www.dotnetwire.com
<crispy> wrote in message news:95********************************@4ax.com...
On this page, you'll see that some PubID's are repeated several times:
http://www.ihea.info/pages/website/test1.asp

That's because some records have more than one attribute. For example,
Title with pubID 10 is about both Cervical Cancer and Endometrial
Cancer, so pubID 10 appears twice.

How do I make ASP show me only one instance of record 10 and then
simply concantenate Cervical Cancer and Endometrial Cancer?

So it would look like this:

10 North Africa Swahili Cervical Cancer: Stay
Healthy! Cervical Cancer & Endometrial Cancer

Here's my relationship:
http://www.ihea.info/pages/website/testimages/rel.gif
tblDiseaseTitle serves as an intermediary to enable many-to-many
relationship between tblDisease (where the multiple subjects come
from) and tblTitle.

If it is easier to correct this in database, I use Access 2002.

thanks in advance,

crispy

Jul 19 '05 #2
On Mon, 15 Mar 2004 15:44:02 -0500, crispy <> wrote:
On this page, you'll see that some PubID's are repeated several times:
http://www.ihea.info/pages/website/test1.asp

That's because some records have more than one attribute. For example,
Title with pubID 10 is about both Cervical Cancer and Endometrial
Cancer, so pubID 10 appears twice.

How do I make ASP show me only one instance of record 10 and then
simply concantenate Cervical Cancer and Endometrial Cancer?

So it would look like this:

10 North Africa Swahili Cervical Cancer: Stay
Healthy! Cervical Cancer & Endometrial Cancer

Here's my relationship:
http://www.ihea.info/pages/website/testimages/rel.gif
tblDiseaseTitle serves as an intermediary to enable many-to-many
relationship between tblDisease (where the multiple subjects come
from) and tblTitle.

If it is easier to correct this in database, I use Access 2002.


You don't show any structure or database schema so I can't say for
certain you'd want to change your design for this, though simply
normalizing the database might solve this. Otherwise, a SELECT
DISTINCT in your query should get you a single instance of each. Post
the actual query if you're still having troubles.

Jeff
Jul 19 '05 #3
You don't show any structure or database schema so I can't say for
certain you'd want to change your design for this, though simply
normalizing the database might solve this. Otherwise, a SELECT
DISTINCT in your query should get you a single instance of each. Post
the actual query if you're still having troubles.

Jeff

Thanks Jeff for your response.

Here's the SQL of the query:

SELECT tblBrochures.PubID, tblLanguages.LanguageName,
tblTitle.TitleName, tblDisease.DiseaseName, TblRegion.Region
FROM TblRegion RIGHT JOIN ((tblLanguages RIGHT JOIN ((tblTitle LEFT
JOIN (tblDisease RIGHT JOIN tblDiseaseTitle ON tblDisease.DiseaseID =
tblDiseaseTitle.DiseaseID) ON tblTitle.titleID =
tblDiseaseTitle.TitleID) RIGHT JOIN tblBrochures ON tblTitle.titleID =
tblBrochures.Title) ON tblLanguages.LanguageID =
tblBrochures.LanguageID) LEFT JOIN tblLanguageRegion ON
tblLanguages.LanguageID = tblLanguageRegion.LanguageID) ON
TblRegion.RegID = tblLanguageRegion.RegionID;

I changed the "Select" to "Select Distinct", but nothing changed.

By "database schema" do you mean the output created by Analyze /
Documenter ? I did the query and posted it at this address:
http://www.ihea.info/pages/website/q...ptObjects.html

If you see any room for normalizing, please point it out. I thought I
did a lot of reading on normalizing and can't figure out how it could
be normalized further.

Thanks for your help. crispy
Jul 19 '05 #4
On Mon, 15 Mar 2004 15:51:05 -0600, "Manohar Kamath [MVP]"
<mk*****@TAKETHISOUTkamath.com> wrote:
' Initialize previous ID to empty
prevPubID = vbNullstring

' Loop through the entire recordset
Do While Not Rs.EOF
' Set the current pub ID
curPubID = Rs("PubID")

' If the current ID not equal to previous one, create a new paragraph
If curPubID <> prevPubID Then
Response.Write("<P>" & curPubID)
End If

' Write out the attributes
Response.Write(Rs("Title"))

Rs.MoveNext

' Set previous ID to current one
prevPubID = curPubID
Loop


Manohar, thanks for your reply. Exactly, where in the page should put
in your code? Should I get rid of the repeat regions? I could use any
amount of help since I'm pretty confused about this right now. I'm
trying to understand your code using a couple of books I have here.
Thanks again. crispy.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../../Connections/IHEA_DNSLESS.asp" -->
<%
Dim rdsAllTitles
Dim rdsAllTitles_numRows

Set rdsAllTitles = Server.CreateObject("ADODB.Recordset")
rdsAllTitles.ActiveConnection = MM_IHEA_DNSLESS_STRING
rdsAllTitles.Source = "SELECT * FROM qryAllTitles"
rdsAllTitles.CursorType = 0
rdsAllTitles.CursorLocation = 2
rdsAllTitles.LockType = 1
rdsAllTitles.Open()

rdsAllTitles_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
rdsAllTitles_numRows = rdsAllTitles_numRows + Repeat1__numRows
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html;
charset=iso-8859-1">
</head>

<body>
<table width="100%" border="1" cellpadding="5">
<tr bgcolor="#CCCCCC">
<td><strong>PubID</strong></td>
<td><strong>Region</strong></td>
<td><strong>Language</strong></td>
<td><strong>Title</strong></td>
<td><strong>Subject</strong></td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT rdsAllTitles.EOF))
%>
<tr>
<td><%=(rdsAllTitles.Fields.Item("PubID").Value)%> </td>
<td><%=(rdsAllTitles.Fields.Item("Region").Value)% ></td>
<td><%=(rdsAllTitles.Fields.Item("LanguageName").V alue)%></td>
<td><%=(rdsAllTitles.Fields.Item("TitleName").Valu e)%></td>
<td><%=(rdsAllTitles.Fields.Item("DiseaseName").Va lue)%></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rdsAllTitles.MoveNext()
Wend
%>

</table>
</body>
</html>
<%
rdsAllTitles.Close()
Set rdsAllTitles = Nothing
%>

Jul 19 '05 #5
Could using "GROUP BY" in the SQL statement do this to group multiple
records with the same data in the paticular field into thier own "sections"
on the page?

Kind of like:...

SELECT * FROM Table GROUP BY CertainField

Then display something like this:

CertainField
Row of data here
Row of data here
Row of data here
CertainField
Row of data here
Row of data here
Row of data here

I know I wasn't the one who asked, but I like to "get a grip" on it too.
:-)
--

Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com
"Jeff Cochran" <jc*************@naplesgov.com> wrote in message
news:40*****************@msnews.microsoft.com...
On Mon, 15 Mar 2004 15:44:02 -0500, crispy <> wrote:
On this page, you'll see that some PubID's are repeated several times:
http://www.ihea.info/pages/website/test1.asp

That's because some records have more than one attribute. For example,
Title with pubID 10 is about both Cervical Cancer and Endometrial
Cancer, so pubID 10 appears twice.

How do I make ASP show me only one instance of record 10 and then
simply concantenate Cervical Cancer and Endometrial Cancer?

So it would look like this:

10 North Africa Swahili Cervical Cancer: Stay
Healthy! Cervical Cancer & Endometrial Cancer

Here's my relationship:
http://www.ihea.info/pages/website/testimages/rel.gif
tblDiseaseTitle serves as an intermediary to enable many-to-many
relationship between tblDisease (where the multiple subjects come
from) and tblTitle.

If it is easier to correct this in database, I use Access 2002.


You don't show any structure or database schema so I can't say for
certain you'd want to change your design for this, though simply
normalizing the database might solve this. Otherwise, a SELECT
DISTINCT in your query should get you a single instance of each. Post
the actual query if you're still having troubles.

Jeff

Jul 19 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by David Ehmer | last post: by
12 posts views Thread by shank | last post: by
7 posts views Thread by =?Utf-8?B?TG9zdEluTUQ=?= | last post: by
1 post views Thread by Solo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.