group total in a report

26 Feb 2005 4:19 AM

Rajani

Hello,

I have a table(msaccess) with the structure...
job_code        text   6
style           text   10
qty             number
fabrication     text    65
ship_date       date/time

I want to show a report like...

job_code    style   qty     fabrication    ship_date
------------------------------------------------------
05A001      2464    75000   AAAAAAA        5-Jan-2005
05A002      3800    93430   BBBBBBB        5-Jan-2005
05B001      2845     4900   CCCCCCC        5-Jan-2005

                  ----------
                   173330
05A003      2564    45000   DDDDDDD        5-Feb-2005
05B002      2800    65000   EEEEEEE       15-Feb-2005
05C001     P357G     5500   FFFFFFF       15-Feb-2005
                 ------------
                   115500

.....

What could be the query and how can i show an HTML report. I am using ADO
with ASP and VBScript.

thanx in advance

26 Feb 2005 1:24 PM

Bob Barrows [MVP]

Rajani wrote:
Show quote

> Hello,
>
> I have a table(msaccess) with the structure...
> job_code        text   6
> style           text   10
> qty             number
> fabrication     text    65
> ship_date       date/time
>
>
> I want to show a report like...
>
> job_code    style   qty     fabrication    ship_date
> ------------------------------------------------------
> 05A001      2464    75000   AAAAAAA        5-Jan-2005
> 05A002      3800    93430   BBBBBBB        5-Jan-2005
> 05B001      2845     4900   CCCCCCC        5-Jan-2005
>
>                  ----------
>                   173330
> 05A003      2564    45000   DDDDDDD        5-Feb-2005
> 05B002      2800    65000   EEEEEEE       15-Feb-2005
> 05C001     P357G     5500   FFFFFFF       15-Feb-2005
>                 ------------
>                   115500
>
> ....
>
> What could be the query and how can i show an HTML report. I am using
> ADO with ASP and VBScript.
>
>
> thanx in advance

You can use a UNION query inside a subquery for this:

Select job_code, style, qty, fabrication,
ship_date FROM
(
Select 'Detail' As RowType, job_code, style, qty, fabrication,
ship_date, Year(ship_date) As ShipYear,
Month(ship_date) As ShipMonth
FROM Shipments
UNION ALL
Select 'Total' As RowType,'Total','',Sum(qty),'',Null,Year(ship_date),
Month(ship_date)
FROM Shipments
GROUP BY Year(ship_date),Month(ship_date)
) As q
ORDER BY ShipYear, ShipMonth, RowType, ship_date

I would create a saved query with this SQL and save it as qShipmentReport.
If you need to limit the time period used for the report, then you can add
parameters, like this:

Select job_code, style, qty, fabrication,
ship_date FROM
(
Select 'Detail' As RowType, job_code, style, qty, fabrication,
ship_date, Year(ship_date) As ShipYear,
Month(ship_date) As ShipMonth
FROM Shipments
WHERE ship_date BETWEEN [pStart] AND [pYear]
UNION ALL
Select 'Total' As RowType,'Total','',Sum(qty),'',Null,
Year(ship_date),Month(ship_date)
FROM Shipments
WHERE ship_date BETWEEN [pStart] AND [pYear]
GROUP BY Year(ship_date),Month(ship_date)
) As q
ORDER BY ShipYear, ShipMonth, RowType, ship_date

The ASP code to display the report would look like this (tested in IE6 - you
may need to tweak it for other browsers. If so, follow up in a client-side
newsgroup):

<%@ Language=VBScript %>
<%
dim sHTML, dStart, dEnd, cn, rs, arData, sCellTagSt, sCellTagEnd
dim iRow, iCol
dStart=Request.Form("StartDate")
dEnd=Request.Form("EndDate")
if len(dStart) > 0 AND len(dEnd)>0 Then
if IsDate(dStart) AND IsDate(dEnd) then
dStart=cdate(dStart)
dEnd=cdate(dEnd)
Set cn=CreateObject("adodb.connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=P:\ath\to\database.mdb"
set rs=CreateObject("adodb.recordset")
cn.qShipmentReport dStart,dEnd, rs
if not rs.EOF then arData=rs.GetRows
rs.Close:set rs=nothing
cn.Close:set cn=nothing
if IsArray(arData) then
   for iRow = 0 to UBound(arData,2)
    if len(sHTML) = 0 then
     sHTML= "<TR>"
    else
     sHTML=sHTML & "<TR>"
    end if
    if arData(0,iRow)="Total" then
     sCellTagSt="<TH>"
     sCellTagEnd="</TH>"
    else
     sCellTagSt="<TD>"
     sCellTagEnd="</TD>"
    end if
    for iCol=0 to UBound(arData,1)
     sHTML=sHTML & sCellTagSt & arData(iCol,iRow) & _
     sCellTagEnd
    next
    sHTML=sHTML & "</TR>"
   next
else
   sHTML="<tr><td colspan=5>No data was found in this " & _
   "date range</td></tr>"
end if
else
sHTML="<tr><td colspan=5>Invalid Date Entered." & _
"Date must be in YYYY-MM-DD format. Ex. 2004-12-01</td></tr>"
end if
End if
%>
<HTML>
<HEAD>
<TITLE>Shipment Report</TITLE>
</HEAD>
<BODY>
<H1>Shipment Report</H1>
<form method="post">
<span
style="margin-right:20px;font: normal normal bold x-small Arial">
Start Date (YYYY-MM-DD): <input name="StartDate"
value="<%=Request.Form("StartDate")%>">
</span>
<span style="font: normal normal bold x-small Arial">
End Date (YYYY-MM-DD): <input name="EndDate"
value="<%=Request.Form("EndDate")%>">
</span>
<input type="submit" value="Submit">
<input type="button" value="Clear"
onclick="document.getElementById('StartDate').value='';
document.getElementById('EndDate').value='';
document.forms(0).submit()">
</form>
<table border="1"
    style="border-color:black;border-collapse:collapse">
<TR style="background-color:lightblue">
<TH>Job Code</TH>
<TH>Style</TH>
<TH>Quantity</TH>
<TH>Fabrication</TH>
<TH>Ship Date</TH>
</TR>
<%=sHTML%>
</table>
</BODY>
</HTML>

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"