Member Avatar for shaggysc96

I am trying to develope a simple application that will record a persons miles per week and total miles but for several different people. I have the code working to accumulate the total, but the problem is how to I get it to accumulate just for the one person and not for all? How do I get it to accumulate for just one team Name? Code is as follows:

<CFQUERY NAME="Mileage" DATASOURCE="#dsn#">
SELECT weeklyMileage, totalMileage
FROM mileage
</CFQUERY>
cfset totalMileage= 0> 
 <cfloop query="Mileage">
<cfset totalMileage =totalMileage + Mileage.weeklyMileage>
</cfloop>

  <cfquery datasource="#dsn#" dbtype="ODBC">
     Insert 
   Into mileage
   (dateupdated,teamName,teamLeader,email,weeklyMileage,mileageWeekNumber,comments,totalMileage) 
         Values 
    
('#Trim(thetime)#','#Trim(teamName)#','#Trim(teamLeader)#','#Trim(email)#','#Trim(weeklyMileage)#','#Trim(mileageWeekNumber)#','#Trim(comments)#','#Trim(totalMileage)#')
</cfquery>
  • 1 Contributor
  • 1 Reply
  • 199 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by shaggysc96
Member Avatar for shaggysc96

ok, now I am really lost. i put in the code to sum up totalMiles grouped by teamName, and took out the loop,total miles now go to 0, what else am I missing?

<CFQUERY NAME="Mileage" DATASOURCE="#dsn#">
SELECT teamname,weeklyMileage
     , SUM(totalMileage) as teamMiles
  FROM mileage
GROUP 
    BY teamname
 </cfquery>
<CFQUERY NAME="getMileage" DATASOURCE="#dsn#">
SELECT mileageWeek
FROM weeks
</CFQUERY>
<html>
<head>
 <title>Untitled</title>
</head>
<body>
  <cfset weeklyMileage= #form.weeklyMileage#> 

    <cfset thetime = "#dateFormat(now(),'YYYY-MM-DD')#">
  <cfquery datasource="#dsn#" dbtype="ODBC">
     Insert 
   Into mileage
   (dateupdated,teamName,teamLeader,email,weeklyMileage,mileageWeek,comments,totalMileage) 
         Values 

('#Trim(thetime)#','#Trim(teamName)#','#Trim(teamLeader)#','#Trim(email)#','#Trim(weeklyMileage)#','#Trim(getMileage.mileageWeek)#','#Trim(comments)#','#Trim(totalMileage)#')
</cfquery>
Edited by Dani because: Fixed formatting
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.