Query for Profit/Loss (1 Viewer)

[plog]

(IF FieldA = Income, then Amount = Positive, but IF FieldA = Expenditure, then Amount = Negative).

You don't have to program this, you can include it in your data. In tblTransactionType table you will add a new field called something like 'TransactionValue' and the values would be either 1 or -1. Then when you build a query to determine balance you bring in tblTransactionType, multiply the Amount field by TransactionValue and then add them all up. This will make the expenditures negative, the income positive and will allow access to calculate your balance for you.

Which means this SQL will produce your balance at any time:

SELECT SUM(Transaction.Value*Amount) AS Balance FROM tblFinance, tblTransactionType WHERE tblTransactionType.TransactionTypeID = tblFinace.TransactionTypeID;

 

[reddevil1]

plog.

thanks for all your advice and suggestions in this post.
i know my database is much more efficient thanks to your time and effort.
it is very much appreciated.
cheers
reddevil

 

[reddevil1]

I wanted to share with you another way of doing this, using a one sided join.

First I created a query to joins Contract, Renter, Property and Owner.

In frmIncome1 (Copy of original) I then added the new query as part of the recordsource using a one sided join. I finally added the extra fields to the form, and set their enabled status to False, to prevent any edit be made to the fields.

If I select a different contract using the combobox, the details will now change.

Does this seem to be an easier way?

But I am worried that it might bloat the database???

 

[plog]

No, that looks good.