Report Grouping

A

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>
 
K

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



Allan said:
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>
Click to expand...
 
A

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



Kathleen Anderson said:
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 said:
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"[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can one include data from javascript in form 1
Error message "An error has occured in the script on this page" 1
FP form will not save input 2
Receiving code instead of graphics 0
Problem seeing some characters when sending email to Outlook client 60
CSS DOES NOT DISPLAY IN IE7 FROM AN INDEX PAGE 4
Basic Calculations in a Form 3
Date Functionality Missing 3

Top