Help with Queries

[Sunwers]

I tried to create a table that would take the sum of one field and multiply it by another field but I'm Doing something wrong. Below is my table that I'm working from and the SQL I created for the query.

OrderLine Table consist of 4 columns; OrderNum, PartNum, NumOrdered, and QuotedPrice.

Query I need to create is a table that consist of the OrderNum and OrderTotal for each order. The OrderTotal is the SUM of the NumOrdered multiplied by the QuotedPrice.

Here is my SQL that I come up with
SELECT OrderLine.OrderNum, SUM(NumOrdered*QuotedPrice) AS OrderTotal
FROM OrderLine;

Once I try to run the SQL it says "Your query does not include the specified expression 'OrderNum' as part of an aggregate function."



What am I doing wrong....

Oh I'm using Access 2013 too

Thanks,
Sunwers

[pbaldy]

Try

SELECT OrderLine.OrderNum, SUM(NumOrdered*QuotedPrice) AS OrderTotal
FROM OrderLine
GROUP BY OrderNum

[Sunwers]

LOL... I have worked on this all night and couldn't seem to get it to work. I was at my wits end trying to figure out what was wrong. Thank you so much, adding the GROUP BY OrderNum worked great! :-) I think I need to go to bed now and get some rest away from Access for a while. That was really simple after thinking about it, I didn't think that would cause it not to run it correctly by not having the GROUP By at the bottom.

Thanks Again,
Veronica

[pbaldy]

Happy to help!

[orange]

Further to Paul's advice on the Group By, here are a couple of links that may be useful to you.

SQL samples and explanations Lots of sql info

Building a database from a description of the business includes Normalization, entities and relationships

Good luck