I have a specific requirement of a query I'm creating, which is difficult to describe in the title of the thread, but what I was is to create a field in a query which takes a value from the record, but then subtracts the values of all the other records with the same type ('type' being a field) and month (the date being in another field).
To explain in a bit more detail, the records are transactions which I use to create my cash-flow forecast, certain records are categorised as 'Sundry' transactions, for example, 'Photographic Sundries' is a transaction type which forecasts sundry costs for photographic equipment in a given month. What I want is, as I create real transactions with the 'Photographic Sundry' type, it will subtract these from the amount on the sundry category.
In short, if I have a transaction for £5.00 with category 'Sundry', for a date in the month 'Oct-11' and type 'Photographic Sundries' and then I create a transaction for £3.00 with a different category, but also with type 'Photographic Sundries' and month 'Oct-11', then I want this field for the initial sundry transaction to show £2.00, indicating I have £2 left, so that my total for the type 'Photographic Sundries' is still £5.00 for October 2011.
I hope I've somehow managed to explain that properly.
To explain in a bit more detail, the records are transactions which I use to create my cash-flow forecast, certain records are categorised as 'Sundry' transactions, for example, 'Photographic Sundries' is a transaction type which forecasts sundry costs for photographic equipment in a given month. What I want is, as I create real transactions with the 'Photographic Sundry' type, it will subtract these from the amount on the sundry category.
In short, if I have a transaction for £5.00 with category 'Sundry', for a date in the month 'Oct-11' and type 'Photographic Sundries' and then I create a transaction for £3.00 with a different category, but also with type 'Photographic Sundries' and month 'Oct-11', then I want this field for the initial sundry transaction to show £2.00, indicating I have £2 left, so that my total for the type 'Photographic Sundries' is still £5.00 for October 2011.
I hope I've somehow managed to explain that properly.
