Development MS Access SQL Help

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).

 

joins ***

 

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.