Totalling 2 column in 1 table where the information is drawn from 2 tables

Question

I have the 1st table (tblProduct) which contains 6 columns.

ProdID, BrandName, ProdType, Manuf, ProdDescript, Price

This table allocates a price to a product.

The 2nd table (tblTransaction) which has 5 columns.

TransID, TransDate, ClientID, ProdID, Quantity

This table has all the items sold and how many of each.

The 3rd table (tblTransaction Query) which has 4 columns

TransID, Quantity, ProdID, Price

This one was made to correlate the information in the first 2 tables so that there was a price next to the correct Product.

 SELECT tblTransaction.TransID, Quantity, tblProduct.ProdID, Price
 FROM tblTransaction, tblProduct
 WHERE tblTransaction.ProdID =  tblProduct.ProdID;

The 4th table (tblProduct Query) which has 2 columns

ProdID, Total_Quantity

It is what I made to show the total number of each product sold. I want a 3rd column to show the total price of each item X quantity sold. So the total of all of each product ( if that makes sense).

 SELECT ProdID, SUM(Quantity) AS Total_Quantity
 FROM tblTransaction AS Query
 GROUP BY ProdID;

I can provide the DB file and screen shots if that will help. I am new to SQL so please bear with me.

I am currently doing SQL in Microsoft Access. It says at the top Database (Access 2000 file format)

It looks to me like Table 3 and Table 4 are not tables. It looks like they are queries. Are Table 1 and 2 real tables, or what? — simon at rcl
Sorry, yes you are correct. 1 and 2 are the tables with the information in. 3 and 4 are queries. If 3 and 4 can be done in 1 query, that would be great. — user3223861
Since the queries have a different number of columns with different data types they cannot be combined. Wait 5 for total price.. — simon at rcl
thanks, when I run this , it gives me a popup window That says "Enter Parameter Value" In the window it says Price and there is a space to enter something. I dont want to enter something, I want it to give me the total. If I click on ok, it puts the new column in but it is blank. — user3223861
OK. I got confused about what was a table and what was a query. I'll adjust the query in a minute, but it'll be a bit more complex! — simon at rcl

simon at rcl simon at rcl · Accepted Answer · 2014-01-22T15:22:50

 SELECT p.ProdID, SUM(t.Quantity) AS Total_Quantity, sum(t.Quantity * p.Price) as total_value
 FROM tblTransaction AS t
 inner join tblProduct p on p.ProdID = t.ProdID
 GROUP BY ProdID;

Note that this is getting total value, not price. If you want average price, just add another column to the select:

AVG(Price)

Cheers -

(Amended post table/query confusion)

Totalling 2 column in 1 table where the information is drawn from 2 tables

1 Answers