Hi ppenn,
Looks like more of a table schema design problem than a coding problem at this point. In fact you could probably get what you need with no VBA at all.
First, avoid table designs that list multiple, squential instances of an identical attribute (month1, month2, month3, etc.) This type of design violates what is known as "1st normal form" which basically requires that tables not contain repeating groups (month1, month2, month3, etc. is one of the forms that repeating groups take in a table). Their presence typically means you need another table containing the repeating attribute. Repeating groups are a nightmare when you try to write summary queries.
In your case though, I don't think another table is what you need. You need some calculated values (months elapsed, accruing budget amounts), and calculated values generally aren't stored in tables at all (unless you're running a data warehouse with summary historical data or something). They are generated using queries.
To get your months elapsed figure, just store the contract start date, then have a look at the DateDiff function in VBA help. It will give you the difference in months between the contract start date and the current date, for example.
To get your accruing budget figures in a query is trickier and would probably require a cross-tab query where ContractID is your Row Value, Month is you Column Value and Amount is your Value field. Getting "total expenditures by month" figures and a "total expenditures to date" figure (using a summary field in a report) would be pretty straight forward. Getting the Value field to be accumulative, though, is the tricky part. I'd have to play around with that one a bit.
Bottom line though is if your tables are storing contract start dates and expenditure figures, querying your tables to produce summary information by month should be possible uisng SQL alone. Wish I had more time to play with this, but just wanted to toss some thoughts out there for now.
Bob
|
