Rajani said:
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
