troubleshooting Question

Front Page SQL summing problem

Avatar of JoeStone
JoeStone asked on
Web Development SoftwareMicrosoft ApplicationsMicrosoft Access
8 Comments1 Solution466 ViewsLast Modified:
I have the following FP query, that works when I want to sum only one of the fields. For example wtd. However, when I try to add additional summations, such as mtd. I get an error message.
<form BOTID="0" METHOD="POST" action="LaborcUM.asp">
<input type= hidden  name= fpdbr_0_pagingMove value = "   |&lt;   ">
<table border= "0">
<tr>
<td><b>Project</b></td>
<td>
<input TYPE="TEXT" NAME="Project" VALUE="<%=Server.HtmlEncode(Request("Project"))%>">
<font face="MS Sans Serif">&nbsp;</font></td>
</tr>
<tr>
<td><b>Week Ending</b></td>
<td><nobr>
                  
<% if 0 then %>
<% end if %>
<font color="#FF0000">
<!--"This is the code that populates the drop down list" -->
</font>
<%
fp_sQry="SELECT DISTINCT WED FROM LaborCum"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="wplrv2"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="WED"
fp_sMenuValue="WED"
fp_sColTypes="&WED=135&"
fp_iDisplayCols=1
fp_fCustomQuery=True
BOTID=3
fp_iRegion=BOTID
%>
<select NAME="WED" SIZE="1"><option selected><% = Request.Form("WED") %></option>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<option><%=FP_FieldHTML(fp_rs,"WED")%></option>
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
</nobr></td>
</tr>
</table>
<br>
<input TYPE="Submit"></form>


<table width="900" border="1">
<thead>
<tr>
<th ALIGN="LEFT" width= "100">
<p align="center"><b>Week Ending</b></th>

<th ALIGN="LEFT" width= "150"><b>Project</b></th>

<th ALIGN="LEFT" width= "150"><b>Employee Name</b></th>
<th ALIGN="LEFT" width= "100">
<p align="right" width= "100"><b>WTD</b></th>
<th ALIGN="LEFT" width= "100">
<p align="right" width= "100"><b>MTD</b></th>
<th ALIGN="LEFT" width= "100">
<p align="right" width= "100"><b>YTD</b></th>
<th ALIGN="LEFT" width= "100">
<p align="right" width= "100"><b>ITD</b></th>

</tr>
</thead>
<tbody>
            
<% 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 LaborCum WHERE (Project LIKE '::Project::%' AND WED =  '::WED::') ORDER BY ""Employee Name"" ASC,Project ASC,wtd ASC"
fp_sDefault="Project=SR06.000.000000&WED=01/05/2007"
fp_sNoRecords="<tr><td colspan=4 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="wplrv2"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="WED"
fp_sMenuValue="WED"
fp_sColTypes="&ID=3&Employee Name=202&wtd=5&Project=202&WED=135&mtd=5&ytd=5&itd=5&"
fp_iDisplayCols=4
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<tr>
<td>
<p align="center">
<%=FP_FieldVal(fp_rs,"WED")%></td>
<td>
<%=FP_FieldVal(fp_rs,"Project")%></td>
<td>
<%=FP_FieldVal(fp_rs,"Employee Name")%></td>
<td>
<p align="right">
<%=FormatNumber(FP_FieldVal(fp_rs,"wtd"),1)%></td>

<td>
<p align="right">
<%=FormatNumber(FP_FieldVal(fp_rs,"mtd"),2)%></td>

<td>
<p align="right">
<%=FormatNumber(FP_FieldVal(fp_rs,"ytd"),1)%></td>

<td>
<p align="right">
<%=FormatNumber(FP_FieldVal(fp_rs,"itd"),1)%></td>
</tr>
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>

<form method="POST" action="--WEBBOT-SELF--" style="text-align: left">
<input TYPE="hidden" NAME="VTI-GROUP" VALUE="0">
<table width="600" border="1">
<thead>
<tr>
<th ALIGN="LEFT">
<p><b>Total wtd</b></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 *,(SELECT sum(wtd) FROM LaborCum WHERE (Project LIKE '::Project::%'AND WED =  '::WED::')) as [Total wtd] FROM LaborCum WHERE (Project LIKE '::Project::%'AND WED =  '::WED::') ORDER BY wtd ASC "
fp_sDefault="Project=SR06.000.000000&WED=01/05/2007&Project=SR06.000.000000&WED=01/05/2007"
fp_sNoRecords="<tr><td colspan=1 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="wplrv2"
fp_iMaxRecords=1
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="Total wtd"
fp_sMenuValue="Total wtd"
fp_sColTypes="&Total wtd=5&ID=3&Employee Name=202&wtd=5&Project=202&WED=135&mtd=5&ytd=5&itd=5&"
fp_iDisplayCols=1
fp_fCustomQuery=True
BOTID=2
fp_iRegion=BOTID

fp_sQry="SELECT *,(SELECT sum(wtd) FROM LaborCum WHERE (Project LIKE '::Project::%'AND WED =  '::WED::')) as [Total wtd] FROM LaborCum WHERE (Project LIKE '::Project::%'AND WED =  '::WED::') ORDER BY wtd ASC "
fp_sDefault="Project=SR06.000.000000&WED=01/05/2007&Project=SR06.000.000000&WED=01/05/2007"
fp_sNoRecords="<tr><td colspan=1 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="wplrv2"
fp_iMaxRecords=1
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="Total wtd"
fp_sMenuValue="Total wtd"
fp_sColTypes="&Total wtd=5&ID=3&Employee Name=202&wtd=5&Project=202&WED=135&mtd=5&ytd=5&itd=5&"
fp_iDisplayCols=1
fp_fCustomQuery=True
BOTID=2
fp_iRegion=BOTID
%>

<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<tr>
<td>
<p align="right">
<%=FormatNumber(FP_FieldVal(fp_rs,"Total wtd"),1)%></td>
<td>
<p align="right">
<%=FormatNumber(FP_FieldVal(fp_rs,"Total wtd"),1)%></td>



</tr>
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>
</div>
ASKER CERTIFIED SOLUTION
JoeStone

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 8 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 8 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004