Calculate value from two field in third field

Question

I'm trying to create a query, that will calculate sum of products on invoice. I have 3 tables :

• Product (with product's price)
• Invoice (with invoice id)
• Products on invoice (with invoice id, product id and number of particular products)

So in my query I take invoice_id (from invoice), price (from product),number of products sold and invoice_id (from products on invoice) and calculate their product in fourth column. I know I sohuld use 'Totals' but how to achieve that ?

Model:

Answer 1

The following added to your SELECT should do it.

[Product].[price] * [Products on invoice].[number of products on invoice] AS Total

If you include the fields and table names, I can give you a much more accurate statement.

Edit:

SELECT 
  invoice.invoice_id, 
  product.price, 
  products_on_invoice.amount, 
  product.price * products_on_invoice.amount AS Total 
FROM 
  invoice INNER JOIN 
    (products_on_invoice INNER JOIN 
      product 
    ON products_on_invoice.product_id = product.product_id) 
  ON invoice.invoice_id = products_on_invoice.invoice_id