Can someone help me again guys 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
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).
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.