troubleshooting Question

Front Page SQL summing problem

Avatar of JoeStone
JoeStone asked on
Microsoft AccessWeb Development SoftwareMicrosoft Applications
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>
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros