Totals in Querys

[Nick Bradbury]

Hi

Currently I am using a report to show details of Orders that have been paid
for in any specific period, the SQL is as follows

SELECT tblOrders.CustomerID, tblCustomers.Company_Name, tblOrders.OrderID,
tblOrders.Order_Date, tblOrders.Date_Paid, tblOrders.Invoice_Paid,
qryOrder_Details.Copy_Line_Total, qryOrder_Details.Product_Line_Total,
[Copy_Line_Total]+[Product_Line_Total] AS LineTotal, [LineTotal]*0.175 AS
VATCharged
FROM tblCustomers INNER JOIN (tblOrders INNER JOIN qryOrder_Details ON
tblOrders.OrderID = qryOrder_Details.OrderID) ON tblCustomers.CustomerID =
tblOrders.CustomerID
WHERE (((tblOrders.Date_Paid) Between [Start Date] And [End Date]) AND
((tblOrders.Invoice_Paid)=Yes) AND ((tblOrders.Invoice_Cancelled)=No))
ORDER BY tblCustomers.Company_Name;

What I would like to do is summarise the data by order, this query outputs
each line of each order e.g. if OrderNo 1300 has 5 items then there are 5
lines on the report. I just want the totals for Order No 1300.


Hope this makes sense


Nick

 

[John Vinson]

What I would like to do is summarise the data by order, this query outputs
each line of each order e.g. if OrderNo 1300 has 5 items then there are 5
lines on the report. I just want the totals for Order No 1300.

Change it to a Totals query by clicking the Greek Sigma icon on the
query design toolbar (looks like a sideways M). Leave the default
Group By on the Orders table fields, and change it to Sum (or Count,
or whatever is appropriate) on the OrderDetails fields.

John W. Vinson[MVP]

 

[Nick Bradbury]

Problem solved,

Many Thanks

Nick

Change it to a Totals query by clicking the Greek Sigma icon on the
query design toolbar (looks like a sideways M). Leave the default
Group By on the Orders table fields, and change it to Sum (or Count,
or whatever is appropriate) on the OrderDetails fields.

John W. Vinson[MVP]

 

[Nick Bradbury]

Problem solved. Many Thanks

Nick

Change it to a Totals query by clicking the Greek Sigma icon on the
query design toolbar (looks like a sideways M). Leave the default
Group By on the Orders table fields, and change it to Sum (or Count,
or whatever is appropriate) on the OrderDetails fields.

John W. Vinson[MVP]