View Full Version : Access trouble (Well, actually aggregate sql trouble)

Feb 3rd, 2007, 10:42 PM
Hey guys,
I'm having some troubles with a query I'm trying to perform in access. Its actually more of a sql problem, but let me lay it out.
I have 3 tables: Customers, Order, and Order Detail. Now, I need to get the total of all sales, average of sales, minimum and maximum of sales for each company. There are a lot of fields and records, but I will only list the ones in use:
Customers: CompanyName
Order: OrderID[fk], CustomerID[fk]
Order Detail: UnitPrice, Quantity, Discount
As you can see, the Order is the joining table between the Customers and the Order Detail.
Ok, so lets lay out a quick record scenario. In the order table, company X has placed order 1. Order Detail contains 4 records (the product to purchase) for order 1, each with a unitPrice, quantity and discount. Now, summing this is a piece of cake, I just take the unitPrice * quantity and subtract the discount. Thats for each item in the order.
However, the trouble comes from trying to average, max and min these values. I cannot get these to work on an order by order basis in a single query. Every attempt at searching to find the min, max, and avg for these records results in the values searching for the min, max and avg for each record in the Order Detail table. I want it to calculate these based on the sum of each order detail, not each record of the order detail.

Any ideas? Any questions?
Any help you could give would be greatly appreciated. I'll pump out the queries that I'm attempting to use as soon as I port this access into a different database since I don't have access on my pc.

Oh, thought I should mention as well. This is part of a school assignment for me. I can do most of it without trouble, but any idea in helping me to figure this out is more what I'm looking for, not the solution.

Ok, this is my current sql (not built in access) to solve for the sum. Any ideas as to how to get the avg, min and max using the Total value?

SELECT `customers`.`companyname`, `order`.`orderid`,
SUM((`order detail`.`unitprice` * `order detail`.`quantity`) - ((`order detail`.`unitprice` * `order detail`.`quantity`) * `order detail`.`discount`)) AS 'Total'
FROM `customers`, `order`, `order detail`
WHERE (`customers`.`customerid`=`order`.`customerid` and `order detail`.`orderid`=`order`.`orderid`)
GROUP BY `customers`.`companyname`