Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 1 of 1
  1. #1
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,978
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts

    Access trouble (Well, actually aggregate sql trouble)

    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.

    [edit]
    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.

    [edit]
    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?
    Code:
    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`
    Last edited by Fou-Lu; 02-03-2007 at 11:25 PM.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 


 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •