Monthly reports

[LearnersPermit]

I'm working on an application which tracks the time that staff spend on specific projects. Users will record the number of hours that they spend each day on their various projects. At the end of the month I wish to have a summary report that will summarize the number of hours spent each month on projects.

I am able to prepare a report for one month but have not been able to show more than one month or to summarize the hours spent on a specific project by all staff. My report should look like

Project Jan. Feb. Mar. Apr. May , etc.
Project 1 25 33 22 66
Project 2

My Code
<cfquery name="qsummary" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
select record.recid,record.day,record.hours_worked,record.projid,project.projid,project.project
from Record,
Project
where record.projid=project.projid
and record.day between <cfqueryparam cfsqltype="cf_sql_date" value="June 1, 2006"> and <cfqueryparam cfsqltype="cf_sql_date" value="June 30, 2006">
order by record.day

</cfquery>

<table>
<tr>
<td>
<cfoutput>
<p class="gen">Monthly Summary Report</p>
<table border=1 cellspacing=0 align="left">
<tr>
<th>Project</th>
<th>June</th>
</tr>
<cfloop query="qsummary">
<tr>
<td><p class="gen">#qsummary.project#</p></td>

<td align="center"><p class="gen">#qsummary.hours_worked#</p></td>
</tr>
</cfloop>

</cfoutput>

Thank you for your assistance.

 

[unclerico]

two things:

1) you are using coldfusion and this is the ASP forum
2) you will need a crosstab/pivot table to do what you want

 

[LearnersPermit]

No wonder I couldn't find the answer. Sorry I'll move the right discussion group and will try your second suggestion as well.

 

[unclerico]

right on...good luck