Design/Concept support please

[Chipster]

Hi,

I'm fairly new and am seeking some tips on setting up a new database
that needs to capture similar quarterly planning dollars at 3 different
points in time or Status (my idea so far) - these three would be
Historic, Official and Proposed.

I have one table with the Historic data that contains
Region, Project, P_Type, Sub_Project, Q1, Q2, Q3 and Q4

My first step was going to be add Funding_Date and Funding_Status and
try and keep all data in one table - but that would not be good design
and would be limitong on using forms to compare Funding_Status
(Historic, Official and Proposed) on one form. This comparison is a
necessary feature to help identify what dollars are changing and at
what point in time and in what quarter.

I know I need to create at least one new table but what is throwing me
off is a process that will be needed - when the Proposed plan is
approved, the status will be changed to Official. Prior to clicking
the button to update this value, I want the Official data changed to
Historic (update qry), and for any subsequent changes, the Historic
value needs to be unique/changed or something to diiferentiate between
different data sets. I am not able to get the concept of how to change
one of the historic data sets by either incrementing the Historic value
(Historic + Date) or (Historic plus new field of autonumber) or
something I'm not thinking of. Any ideas, relatively easy, as to how
to increment this Historic set so there can be multiple sets would be
great.

At the end of a year, of the possible 5-6 data sets, 4-5 would be
Historic, one would be Official and in an ideal world, there would not
be a Proposed once all the planning data acted on. Since few of us are
in this perfect world, of 6 data sets, I'd say 4 data sets would be
Historic, 1 Official and 1 Proposed.

Thanks for any advice!

Chip

 

[mnature]

A quick way to design this would be:

tbl_Projects
ProjectID (PK)
ProjectInfo, etc.

tbl_Planning
PlanningID (PK)
ProjectID
Date
HistoricMoney
OfficialMoney
ProposedMoney

However, if you need to keep any other information about the funding of
historic, offical and proposed, you could keep the tbl_Projects, and have
these tables:

tbl_Planning
PlanningID (PK)
ProjectID
Date
HistoricMoneyID
OfficialMoneyID
ProposedMoneyID

tbl_HistoricMoney
HistoricMoneyID (PK)
HistoricMoneyInfo, etc.

tbl_OfficialMoney
OfficalMoneyID (PK)
OfficalMoneyInfo, etc.

tbl_ProposedMoney
ProposedMoneyID (PK)
ProposedMoneyInfo, etc.

These would allow you to have specific information about the various moneys
that could be updated in the various quarters, without losing that specific
information from a previous quarter. It is possible to make a real monster
of a database, depending on how much information you really want to store and
process. If all you want to do is update fields without keeping historical
information, you might as well use a spreadsheet or a whiteboard.