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 May 16th, 2005, 05:47 AM
Authorized User
 
Join Date: Apr 2005
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default Default values?

Hi,

Hoping someone could help.

I have created a database which records the status & forecast of projects (using a simple traffic light system). This is updated ever month by the relevant owners, and the status & forcast is later exported to a PowerPoint template (to graphically represent each project) and is reported at our monthly management meetings.

Now I would like to incorporate "Months" into the database which would provide a historical reference to see how these projects are progressing on a month to month basis.

Therefore I need to alter the current tables, queries and forms to incorporate this. The idea being the user will select the current reporting month and update the status & forecast fields of those specific projects. When I edit the current form to incorporate this, then I need to manually enter all the project information again and again for every month.

Is there anyway I could have all the project information automatically carried across for each reporting month. I have tried using default values but don't seem to be having much luck.

Any help would be greatly appreciated.
 
Old May 16th, 2005, 07:55 AM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

It sounds like you're trying to de-normalize your database. I'm guessing your database has one table. And each month there are just a few fields that are updated to report the status.

The "correct" structure of your database should be something like:

tblProjects - one-to-many - tblProjectStatus

Put the project information that doesn't change month-to-month in "tblProjects". Give that table a Primary Key. Link tblProjectStatus to that Primary Key by putting a field in tblProjectStatus to hold the value of the Primary Key. tblProjectStatus will contain multiple (many) dates for one project.

There are simple techniques to copy the data from month-to-month. I'll give you a hint but I don't want to explain it because I think it's the wrong route. Simply build an append query that you run one time each month. You really should not be copying "static" data. That is the biggest clue that your database is not normalized.

You might try using Tools | Analyze | Table to get an idea of how to build a more normalized database. Before you start altering your tables with this wizard you should take a backup of your database.

When you get your data normalized you'll have to modify your current reporting queries. But they should be very easy to modify since you only need to extract one of the status records for your currently month reporting properties. It'll be a little more tricky to report the historical status of each project. But that will come easily enough.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org
 
Old May 16th, 2005, 11:48 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Hi,

   I would try this:

tblProject
ProjectID - PK
Etc...


tblProj_Stat
Proj_StatID - PK
ProjectID - FK
StatusID - FK
Date - datetime
etc...

tblProj_Fore
Proj_ForeID - PK
ForecastID - FK
Date - datetime
etc...

tblStatus
StatusID - PK
etc...

tblForecast
ForecastID - PK
etc...


   These junction tables will keep a history of your projects/statuses/forecasts, and allow you to use the Status and Forecast tables as a look ups for the status and forecast values that will become red/yellow/green lights, etc.

   This is also at least 3NF.

HTH



mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
Default Field Values tachtenberg Reporting Services 1 February 19th, 2008 05:52 PM
Default Values On Fields hossrad SQL Server 2005 2 May 15th, 2007 02:27 AM
setting default values prithvi28 XSLT 1 October 3rd, 2006 01:38 PM
Default Values for Query Odifius Access VBA 3 September 20th, 2006 06:30 AM
Default Values davest Access VBA 2 October 19th, 2004 05:57 AM





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