Development MS Access SQL Help

Discussion in 'Software' started by Tibby, 8 May 2006.

  1. Tibby

    Tibby Back Once Again

    Joined:
    9 Oct 2005
    Posts:
    2,882
    Likes Received:
    39
    Can someone help me again guys :p

    I am trying to write an SQL statement, that will bring up information based on a customers name in access.

    I am getting aggregate errors, and I am not sure how to dodge this.

    Code:
    Code:
    SELECT SUM(price*invoice.quantity) AS Total, invoice.quantity
    FROM products, line_items, invoice, customer
    WHERE customer.id=invoice.Customer_ID And invoice.id=line_items.Products_id And products.id=line_items.Products_id And [first name]=[FName]
    ;
    
    Cheers guys
     
  2. John Cena

    John Cena What's a Dremel?

    Joined:
    1 Jun 2004
    Posts:
    818
    Likes Received:
    0
    Aggrate (sp?) queries cannot be combined and must be seperated from other queries. What you can do is seperate the aggrate query into a sub query.

    Heres the fix

    SELECT invoice.quantity, (SELECT SUM(price*invoice.quantity) FROM products WHERE products_id = line_items.Products_id) AS Total
    FROM line_items, invoice, customer
    WHERE customer.id=invoice.Customer_ID And invoice.id=line_items.Products_id And [first name]=[FName]
    ;

    It is standard SQL so it should work in Access (access sucks for DB).

    This looks like it's query a single 2 column record for the customer..you should consider putting this in a stored procedure (No need to put it in a view or inline for performance reasons).
     
  3. Hamish

    Hamish What's a Dremel?

    Joined:
    25 Nov 2002
    Posts:
    3,649
    Likes Received:
    4
  4. John Cena

    John Cena What's a Dremel?

    Joined:
    1 Jun 2004
    Posts:
    818
    Likes Received:
    0
    A SQL join would be waste of performance compared to a subquery. Since his using firstname to query, I bet you it would result in 2 table scans behind the scenes. Also, to use "pure join"...you would have to have 2 or more seperate SQL views or "union" them up.
     

Share This Page