Calculated field help please! (1 Viewer)

[Sketchin]

So I have a query that selects 3 fields: Program Type, Transaction Type, and Amount.

The query picks up and sums 2 different transactions, Budget, and Commitment. The result of the query would look like this:

Program Type | Amount | Transaction Type
CSS | 100 | Budget
CSS | 200 | Commitment


How would I add a 3rd line to this for a new transaction type called "Balance", which would be Budget - Commitment?

I feel like I should know how to do this, but am currently having a massive brain fart.

:banghead:

 

[pbaldy]

I'm guessing in SQL view:

Sum(Budget) - Sum(Commitment) As Balance

 

[The_Doc_Man]

A third line? You can't mean "third field" because you already have three fields. The only way to have a third line returned from a query is if there is a third line in the source table. Queries don't add records (INSERT INTO) and display them at the same time (SELECT).

You can do this in a form or a report because you can do headers & footers for those cases. Not for an actual query though.

 

[Sketchin]

Here is the SQL as it stands:

SELECT tblLookup_ProgramType.ProgramType, Sum(tblProjectTransactions.Amount) AS Commitment, tblLookup_TransactionType.TransactionType
FROM tblProjectTransactions RIGHT JOIN ((tblLookup_TransactionType RIGHT JOIN (tblLookup_ProgramType RIGHT JOIN (tblProject LEFT JOIN tblProjectReport ON tblProject.ProjID = tblProjectReport.ProjID) ON tblLookup_ProgramType.ProgramTypeID = tblProject.ProgramTypeID) ON tblLookup_TransactionType.TransactionTypeID = tblProjectReport.TransactionTypeID) LEFT JOIN tblProjectTransactionBridge ON tblProjectReport.ProjectReportID = tblProjectTransactionBridge.ProjectReportID) ON tblProjectTransactions.TransactionID = tblProjectTransactionBridge.TransactionID
WHERE (((tblProjectReport.TransactionTypeID) In (2,1)))
GROUP BY tblLookup_ProgramType.ProgramType, tblLookup_TransactionType.TransactionType;

 

[pbaldy]

I should have said what Doc said; I'd probably do the math on a form or report. I don't see the SQL you posted having the output you showed.