Home All Groups Group Topic Archive Search About

Report Grouping

microsoft.public.frontpage.programming
Author
4 Feb 2009 7:09 PM
Allan

I need some help creating an ASP report.

I have an Access database with three tables:

WorkOrders
BillsOfLading
Containers

For every ONE WorkOrders record there are multiple BillsOfLading records and
for every BillsOfLading record there are multple Containers records.

I have created the tables with key fields and I have no problem creating a
simple ASP page to show me one Container with its related Bills of Lading and
for each Bill of Lading I display the related containers.

Unfortunately there is no grouping. The Container information is displayed,
then the Bills of Lading are displayed in a table, then the Containers are
displayed in a table. What I would like to do is group the Containers under
the appropriate Bill of Lading.

Any help would be much appreciated.

Here is the way the report looks now:
http://cms.schaferlogistics.com/workorders/workorders_detail.asp?IDWorkOrder=5

Here is the code:


[code]
<html>

<head>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>

<body>

        <table border="0" width="100%" id="table4" cellspacing="0" cellpadding="10">
            <tr>
                <td>
                <p align="center"><font size="4" face="Arial" color="#8B803A">
                Work Order</font></p>
                <p align="center"><font size="4" face="Arial" color="#8B803A">
                Work Order Details</font></p>
                <!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database
Results component on this page is unable to display database content. The
page must have a filename ending in '.asp', and the web must be hosted on a
server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM 01_Q_Work_Orders WHERE (IDWorkOrder = 
::IDWorkOrder::)"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="sms"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue="" fp_sColTypes="&IDWorkOrder=3&IDProfileAccount=3&AccountName=3&WOTotalContainers=3&WOTotalWeight=3&WONotes=203&EnterDate=135&EnterIDUser=3&ModifiedDate=135&ModifiedDateIDUser=3&"
fp_iDisplayCols=11
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<form METHOD="POST"
action="http://cms.schaferlogistics.com/workorders/workorders_bol_add.asp">
                    <table BORDER="0" cellspacing="0" cellpadding="5" width="100%">
                        <tr>
                            <td align="right" width="27%"><b>
                            <font size="2" face="Arial" color="#8B803A">Work Order:</font></b></td>
                            <td>
                            <font face="Arial">
                            <input NAME="IDWorkOrder" SIZE="17"
VALUE="<%=FP_FieldHTML(fp_rs,"IDWorkOrder")%>"></font></td>
                        </tr>
                        <tr>
                            <td align="right" width="27%"><b>
                            <font size="2" face="Arial" color="#8B803A">Account
Name:</font></b></td>
                            <td>
                            <font face="Arial">
                            <input NAME="AccountName" SIZE="40"
VALUE="<%=FP_FieldHTML(fp_rs,"AccountName")%>"></font></td>
                        </tr>
                        <tr>
                            <td align="right" width="27%"><b>
                            <font size="2" face="Arial" color="#8B803A">Total
Containers:</font></b></td>
                            <td>
                            <font face="Arial">
                            <input NAME="WOTotalContainers" SIZE="40"
VALUE="<%=FP_FieldHTML(fp_rs,"WOTotalContainers")%>"></font></td>
                        </tr>
                        <tr>
                            <td align="right" width="27%"><b>
                            <font size="2" face="Arial" color="#8B803A">Total
Weight:</font></b></td>
                            <td>
                            <font face="Arial">
                            <input NAME="WOTotalWeight" SIZE="40"
VALUE="<%=FP_FieldHTML(fp_rs,"WOTotalWeight")%>"></font></td>
                        </tr>
                        <tr>
                            <td align="right" width="27%"><b>
                            <font size="2" face="Arial" color="#8B803A">Notes:</font></b></td>
                            <td>
                            <textarea rows="8" name="WONotes"
cols="43"><%=FP_FieldHTML(fp_rs,"WONotes")%></textarea></td>
                        </tr>
                        <tr>
                            <td COLSPAN="2">
                            <p align="center"> <hr color="#8B803A" size="1">
                            <p align="center">
                            <font face="Arial" color="#8B803A" size="4">Bills of
                            Lading</font><p align="center"><font face="Arial">
                            <input TYPE="submit" NAME="AddBOL" value="Add BOL"></font></td>
                        </tr>
                    </table>
                </form>
                <font size="2" face="Arial">
                <!--#include file="../_fpclass/fpdbrgn2.inc"-->
                </font>
                <table border="0" width="100%" id="table5" cellspacing="0"
cellpadding="0">
                    <tr>
                        <td>
                        <table width="100%" border="1" cellspacing="0" cellpadding="3"
style="border-collapse: collapse" bordercolor="#8B803A">
                            <thead>
                                <tr>
                                    <th bgcolor="#E0DAB4" width="15">
                                    </th>
                                    <th bgcolor="#E0DAB4" width="110">
                                    <font face="Arial" size="2"><b>BOL Number</b></font></th>
                                    <th bgcolor="#E0DAB4" width="152">
                                    <font face="Arial" size="2"><b>Total Containers</b></font></th>
                                    <th bgcolor="#E0DAB4">
                                    <font face="Arial" size="2"><b>Total Weight</b></font></th>
                                    <th bgcolor="#E0DAB4">
                                    <font face="Arial" size="2"><b>Enter Date</b></font></th>
                                    <th bgcolor="#E0DAB4" width="39"> </th>
                                    <th bgcolor="#E0DAB4" width="45"> </th>
                                    <th bgcolor="#E0DAB4" width="95"> </th>
                                </tr>
                            </thead>
                            <tbody>
                                <!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database
Results component on this page is unable to display database content. The
page must have a filename ending in '.asp', and the web must be hosted on a
server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM 01_Q_Work_Order_BOL WHERE (IDWorkOrder = 
::IDWorkOrder::)"
fp_sDefault="IDWorkOrder="
fp_sNoRecords="<tr><td colspan=5 align=""LEFT"" width=""100%"">No Bills of
Lading Attached</td></tr>"
fp_sDataConn="sms"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="BOLNumber"
fp_sMenuValue="BOLNumber" fp_sColTypes="&IDWorkOrder=3&IDBOL=3&IDUser=3&BOLNumber=202&BOLTotalContainers=3&BOLTotalWeight=3&BOLNotes=203&EnterDate=135&ModifiedDate=135&ModifiedDateIDUser=3&"
fp_iDisplayCols=5
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<tr>
                                    <td width="15">
                            <font face="Arial">
                            <input NAME="IDBOL" SIZE="1" VALUE="<%=FP_FieldHTML(fp_rs,"IDBOL")%>"
style="color: #FFFFFF; border: 1px solid #FFFFFF"></font></td>
                                    <td width="110">
                                    <p align="center">
                                    <font face="Arial" size="2">
                                    <%=FP_FieldVal(fp_rs,"BOLNumber")%></font></td>
                                    <td align="center" width="152">
                                    <font face="Arial" size="2">
                                    <%=FP_FieldVal(fp_rs,"BOLTotalContainers")%></font></td>
                                    <td align="center">
                                    <font face="Arial" size="2">
                                    <%=FP_FieldVal(fp_rs,"BOLTotalWeight")%></font></td>
                                    <td align="center">
                                    <font face="Arial" size="2">
                                    <%=FP_FieldVal(fp_rs,"EnterDate")%></font></td>
                                    <td align="center" width="39">
                            <b><font size="2" face="Arial">Edit</font></b></td>
                                    <td align="center" width="45">
                                    <b><font size="2" face="Arial">Delete</font></b></td>
                                    <td align="center" width="95">
                                    <b>
                                    <font face="Arial" size="2">
                                    <a
href="workorders_container_add.asp?IDBOL=<%=FP_FieldURL(fp_rs,"IDBOL")%>"
style="text-decoration: none">
                                    Add Container</a></font></b></td>
                                </tr>
                                <!--#include file="../_fpclass/fpdbrgn2.inc"-->
</tbody>
                        </table>
                        <p> </p>
                        <p align="center">
                        <font face="Arial" color="#8B803A" size="4">Containers</font></p>
                <table border="0" width="100%" id="table6" cellspacing="0"
cellpadding="0">
                    <tr>
                        <td>
                        <table width="100%" border="1" cellspacing="0" cellpadding="3"
style="border-collapse: collapse" bordercolor="#8B803A" id="table7">
                            <thead>
                                <tr>
                                    <th bgcolor="#E0DAB4" width="97">
                                    <font face="Arial" size="2"><b>BOL Number</b></font></th>
                                    <th bgcolor="#E0DAB4" width="172">
                                    <font face="Arial" size="2"><b>Container Number</b></font></th>
                                    <th bgcolor="#E0DAB4" width="162">
                                    <font face="Arial" size="2"><b>Total Weight</b></font></th>
                                    <th bgcolor="#E0DAB4" width="136">
                                    <font face="Arial" size="2"><b>Type</b></font></th>
                                    <th bgcolor="#E0DAB4" width="98">
                                    <font face="Arial" size="2"><b>Total Items</b></font></th>
                                    <th bgcolor="#E0DAB4" width="47"> </th>
                                    <th bgcolor="#E0DAB4"> </th>
                                </tr>
                            </thead>
                            <tbody>
                                <!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database
Results component on this page is unable to display database content. The
page must have a filename ending in '.asp', and the web must be hosted on a
server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM 01_Q_Work_Order_BOL_Cont WHERE (IDWorkOrder = 
::IDWorkOrder::)"
fp_sDefault="IDWorkOrder="
fp_sNoRecords="<tr><td colspan=5 align=""LEFT"" width=""100%"">No Containers
Attached</td></tr>"
fp_sDataConn="sms"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="BOLNumber"
fp_sMenuValue="BOLNumber"
fp_iDisplayCols=5
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<tr>
                                    <td width="97">
                                    <p align="center">
                                    <font face="Arial" size="2">
                                    <%=FP_FieldVal(fp_rs,"BOLNumber")%></font></td>
                                    <td align="center" width="172">
                                    <font face="Arial" size="2">
                                    <%=FP_FieldVal(fp_rs,"ContainerNumber")%></font></td>
                                    <td align="center" width="162">
                                    <font face="Arial" size="2">
                                    <%=FP_FieldVal(fp_rs,"ContainerWeight")%></font></td>
                                    <td align="center" width="136">
                                    <font face="Arial" size="2">
                                    <%=FP_FieldVal(fp_rs,"ContainerType")%></font></td>
                                    <td align="center" width="98">
                            </td>
                                    <td align="center" width="47">
                                    <b><font size="2" face="Arial">Edit</font></b></td>
                                    <td align="center">
                                    <b><font size="2" face="Arial">Delete</font></b></td>
                                </tr>
                                <!--#include file="../_fpclass/fpdbrgn2.inc"-->
</tbody>
                        </table>
                        <p> </td>
                    </tr>
                </table>
                        <p> </td>
                    </tr>
                </table>
                <p> </p>
                <p> </p>
                <p> </td>
            </tr>
        </table>

</body>

</html>
[/code]

Author
4 Feb 2009 8:05 PM
Kathleen Anderson
Hi Allan:
I believe this: http://www.spiderwebwoman.com/thingumajig/one_to_many.htm
will do what you are looking for.

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
Expression Web Resources: http://www.spiderwebwoman.com/xweb/
Expression Web Wiki: http://expression-web-wiki.com/
FrontPage Resources: http://www.spiderwebwoman.com/resources/
Please reply to the newsgroup for the benefit of others



Show quoteHide quote
"Allan" <Al***@discussions.microsoft.com> wrote in message
news:2175B625-ABDC-4360-B83C-CCBEEDE1AC01@microsoft.com...
>I need some help creating an ASP report.
>
> I have an Access database with three tables:
>
> WorkOrders
> BillsOfLading
> Containers
>
> For every ONE WorkOrders record there are multiple BillsOfLading records
> and
> for every BillsOfLading record there are multple Containers records.
>
> I have created the tables with key fields and I have no problem creating a
> simple ASP page to show me one Container with its related Bills of Lading
> and
> for each Bill of Lading I display the related containers.
>
> Unfortunately there is no grouping. The Container information is
> displayed,
> then the Bills of Lading are displayed in a table, then the Containers are
> displayed in a table. What I would like to do is group the Containers
> under
> the appropriate Bill of Lading.
>
> Any help would be much appreciated.
>
> Here is the way the report looks now:
> http://cms.schaferlogistics.com/workorders/workorders_detail.asp?IDWorkOrder=5
>
> Here is the code:
>
>
> [code]
> <html>
>
> <head>
> <meta name="GENERATOR" content="Microsoft FrontPage 6.0">
> <meta name="ProgId" content="FrontPage.Editor.Document">
> <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
> <title>New Page 1</title>
> </head>
>
> <body>
>
> <table border="0" width="100%" id="table4" cellspacing="0"
> cellpadding="10">
> <tr>
> <td>
> <p align="center"><font size="4" face="Arial" color="#8B803A">
> Work Order</font></p>
> <p align="center"><font size="4" face="Arial" color="#8B803A">
> Work Order Details</font></p>
> <!--#include file="../_fpclass/fpdblib.inc"-->
> <% if 0 then %>
> <SCRIPT Language="JavaScript">
> document.write("<div style='background: yellow; color: black;'>The
> Database
> Results component on this page is unable to display database content. The
> page must have a filename ending in '.asp', and the web must be hosted on
> a
> server that supports Active Server Pages.</div>");
> </SCRIPT>
> <% end if %>
> <%
> fp_sQry="SELECT * FROM 01_Q_Work_Orders WHERE (IDWorkOrder =
> ::IDWorkOrder::)"
> fp_sDefault=""
> fp_sNoRecords="No records returned."
> fp_sDataConn="sms"
> fp_iMaxRecords=256
> fp_iCommandType=1
> fp_iPageSize=0
> fp_fTableFormat=False
> fp_fMenuFormat=False
> fp_sMenuChoice=""
> fp_sMenuValue=""
> fp_sColTypes="&IDWorkOrder=3&IDProfileAccount=3&AccountName=3&WOTotalContainers=3&WOTotalWeight=3&WONotes=203&EnterDate=135&EnterIDUser=3&ModifiedDate=135&ModifiedDateIDUser=3&"
> fp_iDisplayCols=11
> fp_fCustomQuery=False
> BOTID=0
> fp_iRegion=BOTID
> %>
> <!--#include file="../_fpclass/fpdbrgn1.inc"-->
> <form METHOD="POST"
> action="http://cms.schaferlogistics.com/workorders/workorders_bol_add.asp">
> <table BORDER="0" cellspacing="0" cellpadding="5" width="100%">
> <tr>
> <td align="right" width="27%"><b>
> <font size="2" face="Arial" color="#8B803A">Work Order:</font></b></td>
> <td>
> <font face="Arial">
> <input NAME="IDWorkOrder" SIZE="17"
> VALUE="<%=FP_FieldHTML(fp_rs,"IDWorkOrder")%>"></font></td>
> </tr>
> <tr>
> <td align="right" width="27%"><b>
> <font size="2" face="Arial" color="#8B803A">Account
> Name:</font></b></td>
> <td>
> <font face="Arial">
> <input NAME="AccountName" SIZE="40"
> VALUE="<%=FP_FieldHTML(fp_rs,"AccountName")%>"></font></td>
> </tr>
> <tr>
> <td align="right" width="27%"><b>
> <font size="2" face="Arial" color="#8B803A">Total
> Containers:</font></b></td>
> <td>
> <font face="Arial">
> <input NAME="WOTotalContainers" SIZE="40"
> VALUE="<%=FP_FieldHTML(fp_rs,"WOTotalContainers")%>"></font></td>
> </tr>
> <tr>
> <td align="right" width="27%"><b>
> <font size="2" face="Arial" color="#8B803A">Total
> Weight:</font></b></td>
> <td>
> <font face="Arial">
> <input NAME="WOTotalWeight" SIZE="40"
> VALUE="<%=FP_FieldHTML(fp_rs,"WOTotalWeight")%>"></font></td>
> </tr>
> <tr>
> <td align="right" width="27%"><b>
> <font size="2" face="Arial" color="#8B803A">Notes:</font></b></td>
> <td>
> <textarea rows="8" name="WONotes"
> cols="43"><%=FP_FieldHTML(fp_rs,"WONotes")%></textarea></td>
> </tr>
> <tr>
> <td COLSPAN="2">
> <p align="center"> <hr color="#8B803A" size="1">
> <p align="center">
> <font face="Arial" color="#8B803A" size="4">Bills of
> Lading</font><p align="center"><font face="Arial">
> <input TYPE="submit" NAME="AddBOL" value="Add BOL"></font></td>
> </tr>
> </table>
> </form>
> <font size="2" face="Arial">
> <!--#include file="../_fpclass/fpdbrgn2.inc"-->
> </font>
> <table border="0" width="100%" id="table5" cellspacing="0"
> cellpadding="0">
> <tr>
> <td>
> <table width="100%" border="1" cellspacing="0" cellpadding="3"
> style="border-collapse: collapse" bordercolor="#8B803A">
> <thead>
> <tr>
> <th bgcolor="#E0DAB4" width="15">
> </th>
> <th bgcolor="#E0DAB4" width="110">
> <font face="Arial" size="2"><b>BOL Number</b></font></th>
> <th bgcolor="#E0DAB4" width="152">
> <font face="Arial" size="2"><b>Total Containers</b></font></th>
> <th bgcolor="#E0DAB4">
> <font face="Arial" size="2"><b>Total Weight</b></font></th>
> <th bgcolor="#E0DAB4">
> <font face="Arial" size="2"><b>Enter Date</b></font></th>
> <th bgcolor="#E0DAB4" width="39"> </th>
> <th bgcolor="#E0DAB4" width="45"> </th>
> <th bgcolor="#E0DAB4" width="95"> </th>
> </tr>
> </thead>
> <tbody>
> <!--#include file="../_fpclass/fpdblib.inc"-->
> <% if 0 then %>
> <SCRIPT Language="JavaScript">
> document.write("<div style='background: yellow; color: black;'>The
> Database
> Results component on this page is unable to display database content. The
> page must have a filename ending in '.asp', and the web must be hosted on
> a
> server that supports Active Server Pages.</div>");
> </SCRIPT>
> <% end if %>
> <%
> fp_sQry="SELECT * FROM 01_Q_Work_Order_BOL WHERE (IDWorkOrder =
> ::IDWorkOrder::)"
> fp_sDefault="IDWorkOrder="
> fp_sNoRecords="<tr><td colspan=5 align=""LEFT"" width=""100%"">No Bills of
> Lading Attached</td></tr>"
> fp_sDataConn="sms"
> fp_iMaxRecords=0
> fp_iCommandType=1
> fp_iPageSize=0
> fp_fTableFormat=True
> fp_fMenuFormat=False
> fp_sMenuChoice="BOLNumber"
> fp_sMenuValue="BOLNumber"
> fp_sColTypes="&IDWorkOrder=3&IDBOL=3&IDUser=3&BOLNumber=202&BOLTotalContainers=3&BOLTotalWeight=3&BOLNotes=203&EnterDate=135&ModifiedDate=135&ModifiedDateIDUser=3&"
> fp_iDisplayCols=5
> fp_fCustomQuery=False
> BOTID=0
> fp_iRegion=BOTID
> %>
> <!--#include file="../_fpclass/fpdbrgn1.inc"-->
> <tr>
> <td width="15">
> <font face="Arial">
> <input NAME="IDBOL" SIZE="1" VALUE="<%=FP_FieldHTML(fp_rs,"IDBOL")%>"
> style="color: #FFFFFF; border: 1px solid #FFFFFF"></font></td>
> <td width="110">
> <p align="center">
> <font face="Arial" size="2">
> <%=FP_FieldVal(fp_rs,"BOLNumber")%></font></td>
> <td align="center" width="152">
> <font face="Arial" size="2">
> <%=FP_FieldVal(fp_rs,"BOLTotalContainers")%></font></td>
> <td align="center">
> <font face="Arial" size="2">
> <%=FP_FieldVal(fp_rs,"BOLTotalWeight")%></font></td>
> <td align="center">
> <font face="Arial" size="2">
> <%=FP_FieldVal(fp_rs,"EnterDate")%></font></td>
> <td align="center" width="39">
> <b><font size="2" face="Arial">Edit</font></b></td>
> <td align="center" width="45">
> <b><font size="2" face="Arial">Delete</font></b></td>
> <td align="center" width="95">
> <b>
> <font face="Arial" size="2">
> <a
> href="workorders_container_add.asp?IDBOL=<%=FP_FieldURL(fp_rs,"IDBOL")%>"
> style="text-decoration: none">
> Add Container</a></font></b></td>
> </tr>
> <!--#include file="../_fpclass/fpdbrgn2.inc"-->
> </tbody>
> </table>
> <p> </p>
> <p align="center">
> <font face="Arial" color="#8B803A" size="4">Containers</font></p>
> <table border="0" width="100%" id="table6" cellspacing="0"
> cellpadding="0">
> <tr>
> <td>
> <table width="100%" border="1" cellspacing="0" cellpadding="3"
> style="border-collapse: collapse" bordercolor="#8B803A" id="table7">
> <thead>
> <tr>
> <th bgcolor="#E0DAB4" width="97">
> <font face="Arial" size="2"><b>BOL Number</b></font></th>
> <th bgcolor="#E0DAB4" width="172">
> <font face="Arial" size="2"><b>Container Number</b></font></th>
> <th bgcolor="#E0DAB4" width="162">
> <font face="Arial" size="2"><b>Total Weight</b></font></th>
> <th bgcolor="#E0DAB4" width="136">
> <font face="Arial" size="2"><b>Type</b></font></th>
> <th bgcolor="#E0DAB4" width="98">
> <font face="Arial" size="2"><b>Total Items</b></font></th>
> <th bgcolor="#E0DAB4" width="47"> </th>
> <th bgcolor="#E0DAB4"> </th>
> </tr>
> </thead>
> <tbody>
> <!--#include file="../_fpclass/fpdblib.inc"-->
> <% if 0 then %>
> <SCRIPT Language="JavaScript">
> document.write("<div style='background: yellow; color: black;'>The
> Database
> Results component on this page is unable to display database content. The
> page must have a filename ending in '.asp', and the web must be hosted on
> a
> server that supports Active Server Pages.</div>");
> </SCRIPT>
> <% end if %>
> <%
> fp_sQry="SELECT * FROM 01_Q_Work_Order_BOL_Cont WHERE (IDWorkOrder =
> ::IDWorkOrder::)"
> fp_sDefault="IDWorkOrder="
> fp_sNoRecords="<tr><td colspan=5 align=""LEFT"" width=""100%"">No
> Containers
> Attached</td></tr>"
> fp_sDataConn="sms"
> fp_iMaxRecords=0
> fp_iCommandType=1
> fp_iPageSize=0
> fp_fTableFormat=True
> fp_fMenuFormat=False
> fp_sMenuChoice="BOLNumber"
> fp_sMenuValue="BOLNumber"
> fp_iDisplayCols=5
> fp_fCustomQuery=False
> BOTID=0
> fp_iRegion=BOTID
> %>
> <!--#include file="../_fpclass/fpdbrgn1.inc"-->
> <tr>
> <td width="97">
> <p align="center">
> <font face="Arial" size="2">
> <%=FP_FieldVal(fp_rs,"BOLNumber")%></font></td>
> <td align="center" width="172">
> <font face="Arial" size="2">
> <%=FP_FieldVal(fp_rs,"ContainerNumber")%></font></td>
> <td align="center" width="162">
> <font face="Arial" size="2">
> <%=FP_FieldVal(fp_rs,"ContainerWeight")%></font></td>
> <td align="center" width="136">
> <font face="Arial" size="2">
> <%=FP_FieldVal(fp_rs,"ContainerType")%></font></td>
> <td align="center" width="98">
> </td>
> <td align="center" width="47">
> <b><font size="2" face="Arial">Edit</font></b></td>
> <td align="center">
> <b><font size="2" face="Arial">Delete</font></b></td>
> </tr>
> <!--#include file="../_fpclass/fpdbrgn2.inc"-->
> </tbody>
> </table>
> <p> </td>
> </tr>
> </table>
> <p> </td>
> </tr>
> </table>
> <p> </p>
> <p> </p>
> <p> </td>
> </tr>
> </table>
>
> </body>
>
> </html>
> [/code]
Are all your drivers up to date? click for free checkup

Author
4 Feb 2009 9:32 PM
Allan
Thanks Kathleen.

I could not get the code from the link to work but I did find an alternative
that worked here:

http://www.outfront.net/spooky/adv_grouping.htm



Show quoteHide quote
"Kathleen Anderson" wrote:

> Hi Allan:
> I believe this: http://www.spiderwebwoman.com/thingumajig/one_to_many.htm
> will do what you are looking for.
>
> --
>
> ~ Kathleen Anderson
> Microsoft MVP - FrontPage
> Spider Web Woman Designs
> Expression Web Resources: http://www.spiderwebwoman.com/xweb/
> Expression Web Wiki: http://expression-web-wiki.com/
> FrontPage Resources: http://www.spiderwebwoman.com/resources/
> Please reply to the newsgroup for the benefit of others
>
>
>
> "Allan" <Al***@discussions.microsoft.com> wrote in message
> news:2175B625-ABDC-4360-B83C-CCBEEDE1AC01@microsoft.com...
> >I need some help creating an ASP report.
> >
> > I have an Access database with three tables:
> >
> > WorkOrders
> > BillsOfLading
> > Containers
> >
> > For every ONE WorkOrders record there are multiple BillsOfLading records
> > and
> > for every BillsOfLading record there are multple Containers records.
> >
> > I have created the tables with key fields and I have no problem creating a
> > simple ASP page to show me one Container with its related Bills of Lading
> > and
> > for each Bill of Lading I display the related containers.
> >
> > Unfortunately there is no grouping. The Container information is
> > displayed,
> > then the Bills of Lading are displayed in a table, then the Containers are
> > displayed in a table. What I would like to do is group the Containers
> > under
> > the appropriate Bill of Lading.
> >
> > Any help would be much appreciated.
> >
> > Here is the way the report looks now:
> > http://cms.schaferlogistics.com/workorders/workorders_detail.asp?IDWorkOrder=5
> >
> > Here is the code:
> >
> >
> > [code]
> > <html>
> >
> > <head>
> > <meta name="GENERATOR" content="Microsoft FrontPage 6.0">
> > <meta name="ProgId" content="FrontPage.Editor.Document">
> > <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
> > <title>New Page 1</title>
> > </head>
> >
> > <body>
> >
> > <table border="0" width="100%" id="table4" cellspacing="0"
> > cellpadding="10">
> > <tr>
> > <td>
> > <p align="center"><font size="4" face="Arial" color="#8B803A">
> > Work Order</font></p>
> > <p align="center"><font size="4" face="Arial" color="#8B803A">
> > Work Order Details</font></p>
> > <!--#include file="../_fpclass/fpdblib.inc"-->
> > <% if 0 then %>
> > <SCRIPT Language="JavaScript">
> > document.write("<div style='background: yellow; color: black;'>The
> > Database
> > Results component on this page is unable to display database content. The
> > page must have a filename ending in '.asp', and the web must be hosted on
> > a
> > server that supports Active Server Pages.</div>");
> > </SCRIPT>
> > <% end if %>
> > <%
> > fp_sQry="SELECT * FROM 01_Q_Work_Orders WHERE (IDWorkOrder =
> > ::IDWorkOrder::)"
> > fp_sDefault=""
> > fp_sNoRecords="No records returned."
> > fp_sDataConn="sms"
> > fp_iMaxRecords=256
> > fp_iCommandType=1
> > fp_iPageSize=0
> > fp_fTableFormat=False
> > fp_fMenuFormat=False
> > fp_sMenuChoice=""
> > fp_sMenuValue=""
> > fp_sColTypes="&IDWorkOrder=3&IDProfileAccount=3&AccountName=3&WOTotalContainers=3&WOTotalWeight=3&WONotes=203&EnterDate=135&EnterIDUser=3&ModifiedDate=135&ModifiedDateIDUser=3&"
> > fp_iDisplayCols=11
> > fp_fCustomQuery=False
> > BOTID=0
> > fp_iRegion=BOTID
> > %>
> > <!--#include file="../_fpclass/fpdbrgn1.inc"-->
> > <form METHOD="POST"
> > action="http://cms.schaferlogistics.com/workorders/workorders_bol_add.asp">
> > <table BORDER="0" cellspacing="0" cellpadding="5" width="100%">
> > <tr>
> > <td align="right" width="27%"><b>
> > <font size="2" face="Arial" color="#8B803A">Work Order:</font></b></td>
> > <td>
> > <font face="Arial">
> > <input NAME="IDWorkOrder" SIZE="17"
> > VALUE="<%=FP_FieldHTML(fp_rs,"IDWorkOrder")%>"></font></td>
> > </tr>
> > <tr>
> > <td align="right" width="27%"><b>
> > <font size="2" face="Arial" color="#8B803A">Account
> > Name:</font></b></td>
> > <td>
> > <font face="Arial">
> > <input NAME="AccountName" SIZE="40"
> > VALUE="<%=FP_FieldHTML(fp_rs,"AccountName")%>"></font></td>
> > </tr>
> > <tr>
> > <td align="right" width="27%"><b>
> > <font size="2" face="Arial" color="#8B803A">Total
> > Containers:</font></b></td>
> > <td>
> > <font face="Arial">
> > <input NAME="WOTotalContainers" SIZE="40"
> > VALUE="<%=FP_FieldHTML(fp_rs,"WOTotalContainers")%>"></font></td>
> > </tr>
> > <tr>
> > <td align="right" width="27%"><b>
> > <font size="2" face="Arial" color="#8B803A">Total
> > Weight:</font></b></td>
> > <td>
> > <font face="Arial">
> > <input NAME="WOTotalWeight" SIZE="40"
> > VALUE="<%=FP_FieldHTML(fp_rs,"WOTotalWeight")%>"></font></td>
> > </tr>
> > <tr>
> > <td align="right" width="27%"><b>
> > <font size="2" face="Arial" color="#8B803A">Notes:</font></b></td>
> > <td>
> > <textarea rows="8" name="WONotes"
> > cols="43"><%=FP_FieldHTML(fp_rs,"WONotes")%></textarea></td>
> > </tr>
> > <tr>
> > <td COLSPAN="2">
> > <p align="center"> <hr color="#8B803A" size="1">
> > <p align="center">
> > <font face="Arial" color="#8B803A" size="4">Bills of
> > Lading</font><p align="center"><font face="Arial">
> > <input TYPE="submit" NAME="AddBOL" value="Add BOL"></font></td>
> > </tr>
> > </table>
> > </form>
> > <font size="2" face="Arial">
> > <!--#include file="../_fpclass/fpdbrgn2.inc"-->
> > </font>
> > <table border="0" width="100%" id="table5" cellspacing="0"
> > cellpadding="0">
> > <tr>
> > <td>
> > <table width="100%" border="1" cellspacing="0" cellpadding="3"
> > style="border-collapse: collapse" bordercolor="#8B803A">
> > <thead>
> > <tr>
> > <th bgcolor="#E0DAB4" width="15">
> > </th>
> > <th bgcolor="#E0DAB4" width="110">
> > <font face="Arial" size="2"><b>BOL Number</b></font></th>
> > <th bgcolor="#E0DAB4" width="152">
> > <font face="Arial" size="2"><b>Total Containers</b></font></th>
> > <th bgcolor="#E0DAB4">
> > <font face="Arial" size="2"><b>Total Weight</b></font></th>
> > <th bgcolor="#E0DAB4">
> > <font face="Arial" size="2"><b>Enter Date</b></font></th>
> > <th bgcolor="#E0DAB4" width="39"> </th>
> > <th bgcolor="#E0DAB4" width="45"> </th>
> > <th bgcolor="#E0DAB4" width="95"> </th>
> > </tr>
> > </thead>
> > <tbody>
> > <!--#include file="../_fpclass/fpdblib.inc"-->
> > <% if 0 then %>
> > <SCRIPT Language="JavaScript">
> > document.write("<div style='background: yellow; color: black;'>The
> > Database
> > Results component on this page is unable to display database content. The
> > page must have a filename ending in '.asp', and the web must be hosted on
> > a
> > server that supports Active Server Pages.</div>");
> > </SCRIPT>
> > <% end if %>
> > <%
> > fp_sQry="SELECT * FROM 01_Q_Work_Order_BOL WHERE (IDWorkOrder =
> > ::IDWorkOrder::)"
> > fp_sDefault="IDWorkOrder="
> > fp_sNoRecords="<tr><td colspan=5 align=""LEFT"" width=""100%"">No Bills of
> > Lading Attached</td></tr>"
> > fp_sDataConn="sms"
> > fp_iMaxRecords=0
> > fp_iCommandType=1
> > fp_iPageSize=0
> > fp_fTableFormat=True
> > fp_fMenuFormat=False
> > fp_sMenuChoice="BOLNumber"
> > fp_sMenuValue="BOLNumber"
> > fp_sColTypes="&IDWorkOrder=3&IDBOL=3&IDUser=3&BOLNumber=202&BOLTotalContainers=3&BOLTotalWeight=3&BOLNotes=203&EnterDate=135&ModifiedDate=135&ModifiedDateIDUser=3&"
> > fp_iDisplayCols=5
> > fp_fCustomQuery=False
> > BOTID=0
> > fp_iRegion=BOTID
> > %>
> > <!--#include file="../_fpclass/fpdbrgn1.inc"-->
> > <tr>
> > <td width="15">
> > <font face="Arial">
> > <input NAME="IDBOL" SIZE="1" VALUE="<%=FP_FieldHTML(fp_rs,"IDBOL")%>"
> > style="color: #FFFFFF; border: 1px solid #FFFFFF"></font></td>
> > <td width="110">
> > <p align="center">
> > <font face="Arial" size="2">
> > <%=FP_FieldVal(fp_rs,"BOLNumber")%></font></td>
> > <td align="center" width="152">
> > <font face="Arial" size="2">
> > <%=FP_FieldVal(fp_rs,"BOLTotalContainers")%></font></td>
> > <td align="center">
> > <font face="Arial" size="2">
> > <%=FP_FieldVal(fp_rs,"BOLTotalWeight")%></font></td>
> > <td align="center">
> > <font face="Arial" size="2">
> > <%=FP_FieldVal(fp_rs,"EnterDate")%></font></td>
> > <td align="center" width="39">
> > <b><font size="2" face="Arial">Edit</font></b></td>
> > <td align="center" width="45">
> > <b><font size="2" face="Arial">Delete</font></b></td>
> > <td align="center" width="95">
> > <b>
> > <font face="Arial" size="2">
> > <a
> > href="workorders_container_add.asp?IDBOL=<%=FP_FieldURL(fp_rs,"IDBOL")%>"
> > style="text-decoration: none">
> > Add Container</a></font></b></td>
> > </tr>
> > <!--#include file="../_fpclass/fpdbrgn2.inc"-->
> > </tbody>
> > </table>
> > <p> </p>
> > <p align="center">
> > <font face="Arial" color="#8B803A" size="4">Containers</font></p>
> > <table border="0" width="100%" id="table6" cellspacing="0"
> > cellpadding="0">
> > <tr>
> > <td>
> > <table width="100%" border="1" cellspacing="0" cellpadding="3"
> > style="border-collapse: collapse" bordercolor="#8B803A" id="table7">
> > <thead>
> > <tr>
> > <th bgcolor="#E0DAB4" width="97">
> > <font face="Arial" size="2"><b>BOL Number</b></font></th>
> > <th bgcolor="#E0DAB4" width="172">
> > <font face="Arial" size="2"><b>Container Number</b></font></th>
> > <th bgcolor="#E0DAB4" width="162">
> > <font face="Arial" size="2"><b>Total Weight</b></font></th>
> > <th bgcolor="#E0DAB4" width="136">
> > <font face="Arial" size="2"><b>Type</b></font></th>
> > <th bgcolor="#E0DAB4" width="98">
> > <font face="Arial" size="2"><b>Total Items</b></font></th>
> > <th bgcolor="#E0DAB4" width="47"> </th>
> > <th bgcolor="#E0DAB4"> </th>
> > </tr>
> > </thead>
> > <tbody>
> > <!--#include file="../_fpclass/fpdblib.inc"-->
> > <% if 0 then %>
> > <SCRIPT Language="JavaScript">
> > document.write("<div style='background: yellow; color: black;'>The
> > Database
> > Results component on this page is unable to display database content. The
> > page must have a filename ending in '.asp', and the web must be hosted on
> > a
> > server that supports Active Server Pages.</div>");
> > </SCRIPT>
> > <% end if %>
> > <%
> > fp_sQry="SELECT * FROM 01_Q_Work_Order_BOL_Cont WHERE (IDWorkOrder =
> > ::IDWorkOrder::)"
> > fp_sDefault="IDWorkOrder="
> > fp_sNoRecords="<tr><td colspan=5 align=""LEFT"" width=""100%"">No
> > Containers
> > Attached</td></tr>"
> > fp_sDataConn="sms"
> > fp_iMaxRecords=0
> > fp_iCommandType=1
> > fp_iPageSize=0
> > fp_fTableFormat=True
> > fp_fMenuFormat=False
> > fp_sMenuChoice="BOLNumber"
> > fp_sMenuValue="BOLNumber"

Bookmark and Share

Post Other interesting topics
FP will not publish
Hit Counter not Working
Okay, what now?? Cannot publish
Really screwy password problem
how do i set up a film strip in Frontpage?
Backing up websites??
Can FTP but cannot publish -- do I need the FP extensions??
Publishing question
Formatting Numbers in Database Results
Making One Field Required Based on Previous