SubTotal Query

[gtcoglan]

Hi All,

I have been searching through the forum to see how I can do a query that does subtotals, but to much to my avail there has been nothing that specifically answers my question. So here it is. If I have two tables say one called A and the other B. Now in A there are two fields that I want to use and these are item and quantity (The quantity is the number of that item). In the other table I want to use the field rate. Both tables are related by the item. How do I do a subtotal query that gives me the subtotal for an item i.e. rate * quantity, and shows the information in the specific format:

item : quantity : rate : subtotal

Thanks
Greg

 

[Jon K]

SELECT [Table A].Item, [Table A].Quantity, [Table B].Rate, [Table A].Quantity*[Table B].Rate AS Subtotal
FROM [Table A] INNER JOIN [Table B] ON [Table A].Item = [Table B].Item;
.