sum of rows

  • Thread starter Thread starter em
  • Start date Start date
E

em

Is there a way to sum a group of rows based on the
criteria of other Columns? I have a make table query looks
like this after its run

JON TASK LINE AMT EXP BALANCE DESCRIPTION CONTRACT NO
567 ABN 1 $200 $100 $100 etc etc 4567
567 ABN 3 $400 $500 -$100 " 4521
876 BVC 5 $300 $250 $50 " 5214
876 BVC 7 $90 $10 $80 " 5412

There are many many of these transactions. I want to sum
the amount, expense, and balance when the contract, task,
& Job are is the same.
 
You can sum the row as follows:

Select Jon, Task, Line, Amt, Exp, Balance, Amt + Exp + Balance As RowSum
From YourTable
Where ................ ( I don't see anything in your data that would
indicate when a Job, contract, and/or task are completed, but if there is
you can use it in this where clause)
 
There are many many of these transactions. I want to sum
the amount, expense, and balance when the contract, task,
& Job are is the same.
Click to expand...

SELECT Contract, Task, Job,
SUM(Amount) AS TotalAmount,
SUM(Expense) AS TotalAmount,
SUM(Balance) AS TotalBalance,
COUNT(Line) AS NumberOfLines
FROM MyTable
GROUP BY Contract, Task, Job
ORDER BY Contract, Task, Job

By the way, there is no reason to use a make-table query, since you can
carry out the summary on the original table(s) just as easily, although it
is sometimes easier to debug in two steps.

HTH


Tim F
 
Back
Top