How can I split project savings by month?

  • Thread starter Thread starter Brandon Cheal
  • Start date Start date
B

Brandon Cheal

I have three tables. The highest level is the project
detail. There is one record for each project. The next
level is phase. A project can have many phases. Each
phase has an overall savings that I want to break out.
The third level is the division. Each phase can have the
savings allocated to several divisions. So I have a table
that has project number, phase number, division, and
savings amount.
What I need to do is break that savings out over a time
period. For instance, if the project is 12 months, I would
like to break the savings amt. I have entered for each
division equally over that 12 months, or 24 months or
whatever the case may be.
I guess the part that throws me is I want to be able to
run reports that show how much I have saved each year or
each month. So wouldn't I have to add another table at
the lowest level that has project number, phase number
division, savings and month? And how could I get Access to
automatically add those records based on an overall
savings amt. and a start and end date. Please Help!!
Thanks!

Brandon
 
Brandon said:
I have three tables. The highest level is the project
detail. There is one record for each project. The next
level is phase. A project can have many phases. Each
phase has an overall savings that I want to break out.
The third level is the division. Each phase can have the
savings allocated to several divisions. So I have a table
that has project number, phase number, division, and
savings amount.
What I need to do is break that savings out over a time
period. For instance, if the project is 12 months, I would
like to break the savings amt. I have entered for each
division equally over that 12 months, or 24 months or
whatever the case may be.
I guess the part that throws me is I want to be able to
run reports that show how much I have saved each year or
each month. So wouldn't I have to add another table at
the lowest level that has project number, phase number
division, savings and month? And how could I get Access to
automatically add those records based on an overall
savings amt. and a start and end date. Please Help!!
Thanks!
Click to expand...

If you base your forms on queries then the amount can be shown as a
calculated field.

AmtPerPeriod = SavedAmount/NumPeriod
Both these would come from the project table.
With a little more work and if it made sense, NumPeriods could be the number
of records in the time period table

Something could be
 
-----Original Message-----


If you base your forms on queries then the amount can be shown as a
calculated field.

AmtPerPeriod = SavedAmount/NumPeriod
Both these would come from the project table.
With a little more work and if it made sense, NumPeriods could be the number
of records in the time period table

Something could be


.
Click to expand...
What I have is someone entering 1,000 dollars in savings
for project 1, phase 1, division 1 and also 1,000 dollars
for project 1, phase 1, division 2. That is the info I
have stored in the table. I would like to add two fields,
start month and end month (like start Jan-03, end Dec-03).
I want the 1,000 savings to be broken out by those 12
months (or whatever I have in the start date). But I want
it done so that I can run a report to look at savings for
2003. I think this means that I have to create a table at
a lower level with an entry like this:
project 1, phase 1, division 1, Jan-03, $83
project 1, phase 1, division 1, Feb-03, $83

and so on.

The key is, I don't want to have to enter in 12 entries.
I want to enter a start month, an end month, and a total
savings and have Access break that out for me into another
table. Does this make sense?
 
Brandon said:
What I have is someone entering 1,000 dollars in savings
for project 1, phase 1, division 1 and also 1,000 dollars
for project 1, phase 1, division 2. That is the info I
have stored in the table. I would like to add two fields,
start month and end month (like start Jan-03, end Dec-03).
I want the 1,000 savings to be broken out by those 12
months (or whatever I have in the start date). But I want
it done so that I can run a report to look at savings for
2003. I think this means that I have to create a table at
a lower level with an entry like this:
project 1, phase 1, division 1, Jan-03, $83
project 1, phase 1, division 1, Feb-03, $83

and so on.

The key is, I don't want to have to enter in 12 entries.
I want to enter a start month, an end month, and a total
savings and have Access break that out for me into another
table. Does this make sense?
Click to expand...

You will need to add code to create the records. This can be done with an
INSERT INTO or by opening the table and adding the records in a loop that
creates the records
Use dateAdd and a do loop until the date equals the end date.

Since you seem to know the savings in advance it would be just as east to
print your report showing "Savings from " & StartDate & " To " & EndDate & "
" & SavedAmt & " equals " & savedAmt/12...
 
Back
Top