Group By Sql Statement

Below is a statement that works well for me, but I also need fields MatOnHand.PNDesc and MatOnHand.UM to be included. When I do include these fields in the SELECT statement I get:

"You tried to execute a query that does not include the specified expression 'PNDesc' as part of an aggregate function"

MatOnHand.PN is a part number and I need MatOnHand.PNDesc to describe the PN. Is this possible?


MatRS.Open "SELECT MatOnHand.PN, sum(MatTran.Quant) as Quantity, sum(MatTran.cost) as Cost FROM MatTran INNER JOIN MatOnHand ON MatTran.MatNo = MatOnHand.MatNo WHERE MatTran.billed = false AND MatOnHand.Vendor ='" & vendor & "' Group By MatOnHand.PN", MatCN, adOpenKeyset, adLockReadOnly

Thanks,
Brandon

You can include them in the Select statement but they must also be included in the Group By statement. Note however, if any values vary within a given PN, then this will create a smaller aggregate of results since the grouping for the Sum functions will be based on three fields instead of one. If the values for the description fields don't vary amongst any given PN, then there shouldn't be an issue with including them in the Group By statement.

Thanks a lot... its Perfect now...

I have picked up bits and pieces of SQL through message boards and building Queries in MS Access. What is the best way to learn the language?

It is so powerful and I know so little!!

if you check the *** you'll find several good resources, including a SQL tutorial.