Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 16th, 2005, 05:47 AM
Authorized User
 
Join Date: Apr 2005
Location: , , .
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.
Reply With Quote
  #2 (permalink)  
Old May 16th, 2005, 07:55 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
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
Reply With Quote
  #3 (permalink)  
Old May 16th, 2005, 11:48 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 01:01 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.