The BudgetAmount field should not be changed when you "process a payment" in your purchases table or update and existing such transaction. Instead, a calculation field should subtract the total of these values from the Budget amount and it can then be used to display the remaining balance for that budget.
Add a new occurrence of Purchases and link it to budgets by Acc_no = account_code.
Then either of these two expressions would calculate the budget balance if defined in the budget table:
Amount - Sum ( Purchases 2::Amount )
Amount - Purchses 2::sAmount
In the second example, sAmount is a summary field defined to compute the total of Amount in the purchases table. Which version works best for you can depend on your layout design so you might want to experiment with both options to see which works better in your database.
Hi Phil, thanks, but not getting the desired result, what I'm seeking is a Report listing transactions, sorted by Job No., then Account No., with the budget for each Job/Acc combination displaying alongside. At the moment I'm getting only the first record in the budget table for each purchase record.
I've edited original post with another screenshot
well this is way weird, because I see two ...... do you see a table with red text with budget figures? .... maybe yours is cached somehow.... ?
anyhoo, I'll try and upload the screen shot again..(and now i note that when replying, there's an opportunity to upload an image - is this new?)
Yes, it's newly added by ModMan. It's revealed a bug in the software. That's why you see the controls doubled, but I'll gladly put up with that bug to be able to upload more screen shots more easily to posts here.
Your report layout would be based on Purchases 2, not budgets and that way you'll see all the transactions. You can then add fields from budgets to your layout in order to display values from it. Often, these fields would go into a sub summary part so that they report a sub total in a sub heading for each group of transactions.
Thanks Phil - nearly got it sorted now, just some fine-tuning in the sub-summaries..
Hi Phil & symbister - sorry to be jumping in on the party so late, but I feel very fortunate to have found this thread on just the solution I've been working on this past weekend. Thank you!
I re-configured my tables to match symbister's revised structure as recommended by Phil and my report layout works! Reviewing the report however it appears that only Purchases with related Budget records are appearing in the report. This is problematic as I would like to use the report to show all costs for a specific job, whether or not all the accounts were budgeted (as there are hundreds of accounts).
I was thinking that an option could be when one creates a Purchases record, if there is not an associated Budget record for that job it would create one with a budget of $0 but I'm not sure the best way to implement.
Any insight greatly appreciated.