Changing sign (+ or -) in an append query

[Samson]

So, I have a Tenant Transaction table tblTenantTransaction which obviously contains transaction details for tenants.


At the end of the month I want to be able to consolidate the transactions for that month by creating a carried forward (c/f) transaction and a matching bought forward (b/f) transaction on the first of the next month.


I have been able to create the bought forward transaction as the Amount (value) of the transaction is the same sign (positive or negative) as the Sum of Amount in the append query.


My problem comes when I try to create the carried forward transaction as I need to reverse the sign of the amount.


I've tried many ways to get this to happen but to no success. I think it's time to call in the experts!


***************
I've been trying to insert an image of the append query but the site keeps telling me I don't have permissions!?
Any help on that, then I can show you the append query that works!
***************

[Bob Fitz]

I have been able to create the bought forward transaction as the Amount (value) of the transaction is the same sign (positive or negative) as the Sum of Amount in the append query.

It's not usually considered to be good practice to save calculated data. Usually better to do the calculation whenever and wherever it is required.

I've been trying to insert an image of the append query but the site keeps telling me I don't have permissions!?
Any help on that, then I can show you the append query that works!

You could just copy and past the SQL statement of the query.

[Minty]

You have to upload pictures you can't simply paste them in this forum unfortunately, even though it looks like you can.
You could paste the SQL of the query however as text.

[davegri]

Multiplying any signed value by -1 will reverse its sign.

[Samson]

Ha! I knew it had to be something simple like that. Thank you very much, works perfectly.

I've pasted the new SQL here just for reference:

INSERT INTO tblTenantTransaction ( TenantID, Amount, TransactionDate, TenancyID, PropertyID, PropertyDivisionID, TransactionType, Description, TransactionStatus )
SELECT DISTINCTROW tblTenantTransaction.TenantID, Sum(tblTenantTransaction.Amount)*-1 AS [Sum Of Amount], [Forms]![frmTransactionConsolidationCriteria]![EndDate].[Caption] AS Expr1, tblTenantTransaction.TenancyID, tblTenantTransaction.PropertyID, tblTenantTransaction.PropertyDivisionID, "BCF" AS Expr2, "Balance c/f" AS Expr3, "REC" AS Expr4
FROM tblTenantTransaction
GROUP BY tblTenantTransaction.TenantID, [Forms]![frmTransactionConsolidationCriteria]![EndDate].[Caption], tblTenantTransaction.TenancyID, tblTenantTransaction.PropertyID, tblTenantTransaction.PropertyDivisionID, "BCF", "Balance c/f", "REC", tblTenantTransaction.TransactionDate, Format$([tblTenantTransaction].[TransactionDate],'mmmm yyyy'), Year([tblTenantTransaction].[TransactionDate])*12+DatePart('m',[tblTenantTransaction].[TransactionDate])-1
HAVING (((tblTenantTransaction.TransactionDate)>=[Forms]![frmTransactionConsolidationCriteria]![StartDate].[Caption] And (tblTenantTransaction.TransactionDate)<=[Forms]![frmTransactionConsolidationCriteria]![EndDate].[Caption]));

The important bit is:
Sum(tblTenantTransaction.Amount)*-1 AS [Sum Of Amount]

[davegri]

Happy to help. Pretty basic math principle. Good luck with the project!