Rajani wrote:
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_dat e)
) 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_dat e)
FROM Shipments
WHERE ship_date BETWEEN [pStart] AND [pYear]
GROUP BY Year(ship_date) ,Month(ship_dat e)
) 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=VBScri pt %>
<%
dim sHTML, dStart, dEnd, cn, rs, arData, sCellTagSt, sCellTagEnd
dim iRow, iCol
dStart=Request. Form("StartDate ")
dEnd=Request.Fo rm("EndDate")
if len(dStart) > 0 AND len(dEnd)>0 Then
if IsDate(dStart) AND IsDate(dEnd) then
dStart=cdate(dS tart)
dEnd=cdate(dEnd )
Set cn=CreateObject ("adodb.connect ion")
cn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=P:\ath\t o\database.mdb"
set rs=CreateObject ("adodb.records et")
cn.qShipmentRep ort dStart,dEnd, rs
if not rs.EOF then arData=rs.GetRo ws
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,iRo w) & _
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>Inval id Date Entered." & _
"Date must be in YYYY-MM-DD format. Ex. 2004-12-01</td></tr>"
end if
End if
%>
<HTML>
<HEAD>
<TITLE>Shipme nt 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="<%=Reque st.Form("StartD ate")%>">
</span>
<span style="font: normal normal bold x-small Arial">
End Date (YYYY-MM-DD): <input name="EndDate"
value="<%=Reque st.Form("EndDat e")%>">
</span>
<input type="submit" value="Submit">
<input type="button" value="Clear"
onclick="docume nt.getElementBy Id('StartDate') .value='';
document.getEle mentById('EndDa te').value='';
document.forms( 0).submit()">
</form>
<table border="1"
style="border-color:black;bor der-collapse:collap se">
<TR style="backgrou nd-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"
