help with domain aggregate functions 2

[PAULCALLAGHAN]

I'm using Access 2000 and trying to create a query with a single calculation using domain aggregate functions.

The TRANSACTION HISTORY table has the following fields - PRTNUM, TNXDTE, TNXCDE, COST, etc...

I would like to get the sum costs of all transactions in 2004 where the tnxcde = R, and I would like this grouped/segregated by each part number, of which the part number starts with "TR".

 

[Golom]

Select PrtNum, SUM([Cost]) As SumCost

From tblTranHistory

Where Left(PrtNum,2) = 'TR' AND
      Year(TnxDte)   = 2004 AND
      TnxCode        = 'R'

Group By PrtNum

 

[jrbarnett]

SELECT prtnum, tnxdte, tnxcde, sum (cost) As TotalCost
from [transaction history]
where tnxcode = 'R'
group by prtnum, tnxdte, tnxcde

You will need to add extra fields in to the select and group by lines that you haven't mentioned above and change the name of the "tnxcode" field (or tnxcde field in the select and group by lines) to reflect the field's true name.

There is no need to use VBA domain aggregate functions in queries / SQL code because it slows down the execution; a much better way is demonstrated above, using the SQL aggregate functions.

John