Wrox Programmer Forums
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 16th, 2003, 02:50 PM
Posts: n/a
Default Updating table fields

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
Old September 17th, 2003, 11:26 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

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.


Old September 20th, 2003, 06:09 PM
Posts: n/a

Hello Bob Thanks for your help and suggestions I have now sorted the problem

Similar Threads
Thread Thread Starter Forum Replies Last Post
Updating one table with data from another table dirtdog22 Access VBA 1 January 21st, 2008 04:41 PM
Updating Dates and Changing two other fields arholly Access VBA 11 December 7th, 2006 01:31 PM
Updating Excel with Form fields nancy Classic ASP Professional 6 April 7th, 2006 02:18 PM
updating text fields with option buttons redtechcoms Access VBA 7 February 2nd, 2006 03:14 PM
Updating table every now and then rtr1900 Classic ASP Databases 2 December 2nd, 2005 03:12 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.