How to show multiple records in the same line

[crispy]

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

[Manohar Kamath [MVP]]

Re: How to show multiple records in the same line

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

https://www.dotnetwire.com

<crispy> wrote in message news:954c509dv2 mhnnno5hbf4gh8h p3c0367s2@4ax.c om...[color=blue]
> 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
>[/color]

[Jeff Cochran]

Re: How to show multiple records in the same line

On Mon, 15 Mar 2004 15:44:02 -0500, crispy <> wrote:
[color=blue]
>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
>tblDiseaseTitl e 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.[/color]

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

[crispy]

Re: How to show multiple records in the same line

[color=blue]
>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[/color]
Thanks Jeff for your response.

Here's the SQL of the query:

SELECT tblBrochures.Pu bID, tblLanguages.La nguageName,
tblTitle.TitleN ame, tblDisease.Dise aseName, TblRegion.Regio n
FROM TblRegion RIGHT JOIN ((tblLanguages RIGHT JOIN ((tblTitle LEFT
JOIN (tblDisease RIGHT JOIN tblDiseaseTitle ON tblDisease.Dise aseID =
tblDiseaseTitle .DiseaseID) ON tblTitle.titleI D =
tblDiseaseTitle .TitleID) RIGHT JOIN tblBrochures ON tblTitle.titleI D =
tblBrochures.Ti tle) ON tblLanguages.La nguageID =
tblBrochures.La nguageID) LEFT JOIN tblLanguageRegi on ON
tblLanguages.La nguageID = tblLanguageRegi on.LanguageID) ON
TblRegion.RegID = tblLanguageRegi on.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/qry_documentation/doc_rptObjects.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

[crispy]

Re: How to show multiple records in the same line

On Mon, 15 Mar 2004 15:51:05 -0600, "Manohar Kamath [MVP]"
<mkamath@TAKETH ISOUTkamath.com > wrote:
[color=blue]
>' 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[/color]

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="VB SCRIPT" CODEPAGE="1252" %>
<!--#include file="../../Connections/IHEA_DNSLESS.as p" -->
<%
Dim rdsAllTitles
Dim rdsAllTitles_nu mRows

Set rdsAllTitles = Server.CreateOb ject("ADODB.Rec ordset")
rdsAllTitles.Ac tiveConnection = MM_IHEA_DNSLESS _STRING
rdsAllTitles.So urce = "SELECT * FROM qryAllTitles"
rdsAllTitles.Cu rsorType = 0
rdsAllTitles.Cu rsorLocation = 2
rdsAllTitles.Lo ckType = 1
rdsAllTitles.Op en()

rdsAllTitles_nu mRows = 0
%>


<%
Dim Repeat1__numRow s
Dim Repeat1__index

Repeat1__numRow s = -1
Repeat1__index = 0
rdsAllTitles_nu mRows = rdsAllTitles_nu mRows + Repeat1__numRow s
%>



<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Untitl ed 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="#CCCCC C">
<td><strong>Pub ID</strong></td>
<td><strong>Reg ion</strong></td>
<td><strong>Lan guage</strong></td>
<td><strong>Tit le</strong></td>
<td><strong>Sub ject</strong></td>
</tr>
<%
While ((Repeat1__numR ows <> 0) AND (NOT rdsAllTitles.EO F))
%>
<tr>
<td><%=(rdsAllT itles.Fields.It em("PubID").Val ue)%></td>
<td><%=(rdsAllT itles.Fields.It em("Region").Va lue)%></td>
<td><%=(rdsAllT itles.Fields.It em("LanguageNam e").Value)%> </td>
<td><%=(rdsAllT itles.Fields.It em("TitleName") .Value)%></td>
<td><%=(rdsAllT itles.Fields.It em("DiseaseName ").Value)%> </td>
</tr>
<%
Repeat1__index= Repeat1__index+ 1
Repeat1__numRow s=Repeat1__numR ows-1
rdsAllTitles.Mo veNext()
Wend
%>

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

[Phillip Windell]

Re: How to show multiple records in the same line

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]

https://www.wandtv.com

"Jeff Cochran" <jcochran.nospa m@naplesgov.com > wrote in message
news:405e269e.2 856002234@msnew s.microsoft.com ...[color=blue]
> On Mon, 15 Mar 2004 15:44:02 -0500, crispy <> wrote:
>[color=green]
> >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
> >tblDiseaseTitl e 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.[/color]
>
> 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[/color]