Queries or expressions to run a list of totals

[Guest]

My database has a query for purchase orders. It lists items which a customer
has purchased. Another query runs a total of all the items on the purchase
order. I need to run a query of every customer within a specific year and
the total of each purchase order. I've tried different ways to run another
query but it is requiring specific purchase order numbers and lists items. I
don't want anything but a list of customers and the total of the purchase
orders from a specific year (the total comes from an expression in another
query). How would I go about gathering this information? What type of
expression can be used? Am I looking in the wrong direction?

 

[pietlinden]

My database has a query for purchase orders. It lists items which a customer
has purchased. Another query runs a total of all the items on the purchase
order. I need to run a query of every customer within a specific year and
the total of each purchase order. I've tried different ways to run another
query but it is requiring specific purchase order numbers and lists items. I
don't want anything but a list of customers and the total of the purchase
orders from a specific year (the total comes from an expression in another
query). How would I go about gathering this information? What type of
expression can be used? Am I looking in the wrong direction?

Yep, try a report with a summary and detail sections.

 

[Guest]

Ronnie:

Here's an example using the sample Northwind database which might point you
in the right direction:

SELECT [Enter Year:] AS [Order Year], CompanyName,
FORMAT(SUM(UnitPrice*Quantity*(1-[Discount])/100*100),"Currency") AS [Total
Ordered]
FROM (Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID)
INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
WHERE YEAR(OrderDate) = [Enter Year:]
GROUP BY CompanyName;

It prompts for the year when you run it.

Ken Sheridan
Stafford, England

 

[Jamie Collins]

Here's an example using the sample Northwind database which might point you
in the right direction:

SELECT [Enter Year:] AS [Order Year], CompanyName,
FORMAT(SUM(UnitPrice*Quantity*(1-[Discount])/100*100),"Currency") AS [Total
Ordered]
FROM (Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID)
INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
WHERE YEAR(OrderDate) = [Enter Year:]
GROUP BY CompanyName;

It prompts for the year when you run it.

I'm not sure Northwind is a great example here because it uses a
floating point type for Discount (Single) which is bad news for money
data, especially with set functions such as SUM.

[I'm baffled by what your /100*100 is for: coerce (not cast) to
Double? The result is Double anyhow e.g.

SELECT TYPENAME(1 - CSNG(0.1))

returns 'Double'. Double floating point is still bad news for money
data.]

Consider this comparison which uses CURRENCY for Discount; while a
discount *rate* typed as CURRENCY is potentially confusing, it does
keep the results consistent as CURRENCY:

ALTER TABLE [Order Details] ADD
Discount_currency CURRENCY
;
UPDATE [Order Details]
SET Discount_currency = Discount
;

SELECT CompanyName, UnitPrice*Quantity*(1-[Discount]) AS
result_double,
UnitPrice*Quantity*(1-Discount_currency) AS result_currency
FROM (Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID)
INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID;

While you'd get away with the rounding in the above, you aren't so
lucky when tallied:

SELECT CompanyName, SUM(UnitPrice*Quantity*(1-[Discount])) AS
result_double,
SUM(UnitPrice*Quantity*(1-Discount_currency)) AS result_currency
FROM (Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID)
INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
GROUP BY CompanyName
HAVING CCUR(SUM(UnitPrice*Quantity*(1-[Discount]))) <>
SUM(UnitPrice*Quantity*(1-Discount_currency))

For me, the above returns four mismatching rows out of a total of 86
rows

Jamie.

--

 

[Guest]

Thanks! This worked.
--
Ronnie

Yep, try a report with a summary and detail sections.