Great - thanks! I haven't been using Access for even a year yet, and before that I'd never done any programming, so I'm grateful for any thoughts on my work. It's entirely possible that you'll tell me I'm mad trying to do this!!!
Big picture is that the database is used to store and assemble capital and through-life costs of items of furniture used in building projects. This is traditionally done on spreadsheets, but this causes problems with auditability and data storage and retrieval. It is also a problem because the capital and through-life costs are assembled by different departments, who need data presented differently.
OK. The outputs are capital costs, which is straightforward, and through-life costs, which is a row of figures corresponding to spend in subsequent years. I've achieved this through crosstab queries.
To examine affordability of choices of furniture for a particular project, what I need to do is produce a 'deposit' figure, i.e. an amount of money that is put into a bank account to meet all the future expenditure. In Excel this is achieved through a goal-seek function. You have the row of expenditure figures (inflated each year), a closing and opening balance, and the deposit. The Excel model goal-seeks to make the closing balance always be positive at the lowest possible deposit.
So (eventually) to replicate this I've created a recordset which contains the expenditure. I loop through it starting with using £100 (or something suitable) as deposit, and use variables to get the opening and closing balances. If the closing balance is ever less than zero, the code exits the loop. Then I add £10 to the deposit and start again. So basically once the recordset reaches EOF you know that the deposit is right.
If you're still with me (and haven't got bored of my rambling), I need one more thing from the code: all the deposit figures (it gets inflated each year). I then need to add them up and discount them back to present-day value. I don't necessarily need this bit of code to chuck them out, but it might as well, while it's doing it. Hence the need to create a field in my recordset, to hold the information.
If you want to see my code let me know, but I thought I would not include it as this post is already huge!!!
Many thanks for any ideas you can give!
Sarah
