Microsoft Word Reporting Tool

Word Report Template - Account Statement

See Microsoft ® Word ® Report

Word Report Template - Account Statement

Function and SQL

Name1: $BeginOID
Value Sample: 10470
Name1: $EndOID
Value Sample: 10490

DROP TABLE tmp_order

SELECT o.OrderDate
,SUM(d.UnitPrice * d.Quantity * (1-d.Discount)) AS Amount
INTO tmp_order
FROM Orders o, OrderDetails d
WHERE o.OrderID = d.OrderID 
GROUP BY o.OrderDate, o.OrderID, o.CustomerID, o.Freight

DROP TABLE tmp_customer_sum

SELECT c.CustomerID
,SUM(o.Amount + o.Freight) AS TotalAmt
INTO tmp_customer_sum
FROM tmp_order o, Customers c
WHERE o.CustomerID = c.CustomerID 
GROUP BY c.CustomerID

/* Account Statement */
, OrderDate,InvoiceID,OrderID,DueDate,Amount,Freight
range=order group=1,2,3,4,5 NODATA=delrange)
SELECT c.CompanyName AS Customer
,ci.CityName & ', ' & co.CountryName AS City
,o.OrderID AS InvoiceID
,DateAdd('d',30,o.OrderDate) as DueDate
FROM Customers c, Cities ci, Countries co
, tmp_order o, tmp_customer_sum t
WHERE c.CityCode = ci.CityCode 
AND c.CountryCode = ci.CountryCode 
AND c.CountryCode = co.CountryCode 
AND c.CustomerID = o.CustomerID 
AND c.CustomerID = t.CustomerID 
ORDER BY c.CustomerID, o.OrderID

These sample reports are generated by WDReportGen (Report Generator for Microsoft Word). The sample reports in Microsoft Word document format can be download for your report templates, and changed to adapt to your own needs. It is free.

Download the report samples in Microsoft Word document format  DOC (472KB)


Copyright © 2004 - 2014 LJZsoft Corporation. All rights reserved.