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.

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

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