473,224 Members | 1,945 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

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 2265
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: googlinggoogler | last post by:
Hiya, The title says it all really, but im a newbie to python sort of. I can read in files and write files no probs. But what I want to do is read in a couple of files and output them to one...
5
by: David Ehmer | last post by:
The code below is 2 rows in a table, the top row contains a message to be shown if the recordset returns no matches. The 2nd row will display any matches. Problem is that if no matches are found...
2
by: JK | last post by:
Hi All, I am working a form which is tabular layout (continuous form) which will display multiple records at a time. Each line of record needs to be reflected on the color code to show their...
4
by: news.online.no | last post by:
In a query, I need too be able to show if a parent record has a child record. Using the query in a combo box too select record in a form. Thanks :)
1
by: Charlie | last post by:
Hello, I have data in an Access table that I would like to export to multiple HTML tables. I would like to split the data in the Access table (about 92,000 records) into multiple HTML...
12
by: shank | last post by:
I'm trying to use online samples for submitting multiple records from ASP into a stored procedure. Failing! Through the below form, a user could be submitting many records at a time. I'm not...
5
by: jhutchings | last post by:
Hello everyone, I have a database where I collect shipment data from various tables. However, I have a problem. Whenever I want to see shipping data for orders that were set to ship on or before...
52
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the values of multiple fields - get their possible...
7
by: =?Utf-8?B?TG9zdEluTUQ=?= | last post by:
Hi All :) I'm converting VB6 using True DBGrid Pro 8.0 to VB2005 using DataGridView. True DBGrid has a MultipleLines property that controls whether individual records span multiple lines. Is...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.