|
Subject:
|
Updating table fields
|
|
Posted By:
|
ppenn
|
Post Date:
|
9/16/2003 2:50:59 PM
|
I am stuggling with some vba!
I have a contracts table with various columns including the following columns
1) Period in months that the contract has run,
2) 12 columns labelled Month01,Month02,Month03 up to 12 showing accruing budget figures
3) Relevant month
What I have done so far is assign each Month value to a variable and if the elapsed figure is equal or between a month number then allocate that amount to the relevant month field
For example if a contract has run for 3 months then update the relevantmonth field to that equal to the Month03 field.
The ultimate purpose of this is to be able to run a query that will compare the accrued budget figure against accrued costs.
I am having a problem designing the code that will do this update for me
Many thanks for a beginner
Peter
|
|
Reply By:
|
Bob Bedell
|
Reply Date:
|
9/17/2003 11:26:40 AM
|
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
|
|
Reply By:
|
ppenn
|
Reply Date:
|
9/20/2003 6:09:33 PM
|
Hello Bob Thanks for your help and suggestions I have now sorted the problem
Peter
|
