Question
Populating a structure from multiple queries
I have 3 tables of info that share a common column called
"cost code". One table (CostCode) defines each cost code. For
instance
(**NOTE:Rows are seperated by commas)
Column1 - (PrimaryKey) ID=1,2
Column2 - CostCode=555,444
A second table has a number of rows with tasks (Column2) with Manhours(Column3) assigned which are labeled with cost codes in the Cost Code Column (Column4). The value is a primary key value from the "CostCode" table. There are many number of task with no specific limit labeled with the same cost code value. Here is a look.
Column1 - ID=450
Column2 - Task=Build Scaffold,Work on Scaffold
Column3 - CostCode=1(555),2(444)
Column4 - Manhours=20,1
Column5 - ManhoursComplete=15,0
The third is a table of Job Numbers with the same cost codes with Dollars assigned to them. Here is a look.
Column1 - ID=1,2,3
Column2 - JobNumber=2833,3023,3023
Column3 - CostCode=555,444,555
Here is the goal:
I would like to have a query gather the data from the three tables then display them in an HTML table. Each row would be a cost code row as defined below:
<cfloop query=??>
<cfoutput>
Column1 - CostCode = #CostCode#
Column2 - TotalManhours = #(SUM(Manhours))#
Column3 - Dollars =#Dollars#
Column4 - PercentComplete = #(ManhoursComplete/Manhours)#
</cfoutput>
</cfloop>
The problem is obvioulsy, I can only loop one query. I have also tried to build a structure, which I am not very experienced with, to hold all the data and I was pretty close to doing so but could figure out how to loop the data (manhours) into an array. I used an index loop but the out put displayed only one value (first value in the loop) throughout the sequence of array positions numbering the record count.
<cfset ManhoursArray=ArrayNew(1)>
<CFSET TotalRows = TrackingDesc.RecordCount>
<!--- Populate Arrays with values --->
<cfloop query="TrackingDesc">
<cfloop index="Manhours" from=1 to="#TotalRows#">
<cfset ManhoursArray[Manhours]="#Manhours#">
</cfloop></cfloop>
I am up for suggestions and hope I didn't confuse anyone. What would be the best way to display all of these results. NOTE: The database tables have to be structure like this due to large amounts of other data columns in them.
(**NOTE:Rows are seperated by commas)
Column1 - (PrimaryKey) ID=1,2
Column2 - CostCode=555,444
A second table has a number of rows with tasks (Column2) with Manhours(Column3) assigned which are labeled with cost codes in the Cost Code Column (Column4). The value is a primary key value from the "CostCode" table. There are many number of task with no specific limit labeled with the same cost code value. Here is a look.
Column1 - ID=450
Column2 - Task=Build Scaffold,Work on Scaffold
Column3 - CostCode=1(555),2(444)
Column4 - Manhours=20,1
Column5 - ManhoursComplete=15,0
The third is a table of Job Numbers with the same cost codes with Dollars assigned to them. Here is a look.
Column1 - ID=1,2,3
Column2 - JobNumber=2833,3023,3023
Column3 - CostCode=555,444,555
Here is the goal:
I would like to have a query gather the data from the three tables then display them in an HTML table. Each row would be a cost code row as defined below:
<cfloop query=??>
<cfoutput>
Column1 - CostCode = #CostCode#
Column2 - TotalManhours = #(SUM(Manhours))#
Column3 - Dollars =#Dollars#
Column4 - PercentComplete = #(ManhoursComplete/Manhours)#
</cfoutput>
</cfloop>
The problem is obvioulsy, I can only loop one query. I have also tried to build a structure, which I am not very experienced with, to hold all the data and I was pretty close to doing so but could figure out how to loop the data (manhours) into an array. I used an index loop but the out put displayed only one value (first value in the loop) throughout the sequence of array positions numbering the record count.
<cfset ManhoursArray=ArrayNew(1)>
<CFSET TotalRows = TrackingDesc.RecordCount>
<!--- Populate Arrays with values --->
<cfloop query="TrackingDesc">
<cfloop index="Manhours" from=1 to="#TotalRows#">
<cfset ManhoursArray[Manhours]="#Manhours#">
</cfloop></cfloop>
I am up for suggestions and hope I didn't confuse anyone. What would be the best way to display all of these results. NOTE: The database tables have to be structure like this due to large amounts of other data columns in them.
AdChoices