1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Development MS Access SQL Help

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

  1. Tibby

    Tibby Technologic

    Joined:
    9 Oct 2005
    Posts:
    2,876
    Likes Received:
    38
    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 New Member

    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 New Member

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

    John Cena New Member

    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