Query List HELP

[terawebwriter]

I have a database that lists Help Desk tickets. The table consists of Technician Name, Week #, Ticket Count, and Site Name. What I want to do is to list the total # of tickets completed, per technician, per week.
Ex:

Name Week1 Week2 Week3
John Doe 20 Tickets 25 Tickets 15 Tickets
John Doe1 10 Tickets 4 Tickets 22 Tickets
John Doe2 40 Tickets 10 Tickets 3 Tickets

I included what I thought it would work below, but I am either missing something or did not complete the query right. I dont care much about the site, that's for later use. Any help I would appreciate it.

<%@LANGUAGE=&quot;VBSCRIPT&quot;%>
<!--#include file=&quot;../../Connections/Reports.asp&quot; -->
<!-- #include file=&quot;../../Connections/adovbs.inc&quot; -->

<%

Set RS = Server.CreateObject(&quot;ADODB.Recordset&quot
SqlString = &quot;SELECT TicketCount, Week, Site, Technician &quot;
SqlString = SqlString & &quot; FROM TicketC&quot;

Set RS = objConn.Execute ( SqlString )
%>


<%
while not RS.EOF

If RS(&quot;Week&quot = Week1 AND
If RS(&quot;Technician&quot = John Doe then
Response.Write RS(&quot;TicketCount&quot

RS.MoveNext
WEND

%>

What am I doing wrong? What am I missing? Thankx for your help.

 

[denoxis]

try a crosstab query. Should work fine for MS SQL, not sure for Access:

&quot;select technician, case week when 1 then sum(ticketcount) end as Week1Total, case week when 2 then sum(ticketcount) end as Week2Total, case week when 3 then sum(ticketcount) end as Week3Total from table GROUP BY technician, Week1Total, Week2Total, Week3Total&quot;

Then just the list the result.

 

[terawebwriter]

Is asking me for a End of Statement after the When

case week when 1 <===== Expected End of Statement

Its not a SQL Database, its a MS Access Database. Does it matter?

 

[onpnt]

you may just be missing the select
&quot;select technician, case week when 1 then sum(ticketcount) end selectas Week1Total, case week when 2 then sum(ticketcount) end selectas Week2Total, case week when 3 then sum(ticketcount) end selectas Week3Total from table GROUP BY technician, Week1Total, Week2Total, Week3Total&quot;

You cannot mandate productivity, you must provide the tools to let people become their best.
-Steve Jobs
admin@onpntwebdesigns.com

 

[onpnt]

the original way you had it needed to have on less if in the statement, like this
If RS(&quot;Week&quot = Week1 AND RS(&quot;Technician&quot = John Doe then
Response.Write RS(&quot;TicketCount&quot


You cannot mandate productivity, you must provide the tools to let people become their best.
-Steve Jobs
admin@onpntwebdesigns.com