How can I split project savings by month?

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
 
M

Mike Painter

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
 
B

Brandon Cheal

-----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?
 
M

Mike Painter

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...
 
B

Brandon

-----Original Message-----


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...
Click to expand...
Here is the code I have come up with but I have never
used the open recordset method. what am i doing wrong?

I have a field "awardbidamt" that has the savings amt for
the life of the contract. So i added a field for
contract length (in number of months) and a start date.
The start date actual value is an integer that i have
assigned to each month in my time period table. That is
why I add 1 to each value of intMonth to get the next
month's value.
SO I have the table where I assign the savings to a
project number, phase (lot) number, division, and
location, then i created a table with one to many for
those four fields together. I added the savingsmonth
field and the savings field so now I can enter in savings
by month. This following command button is clicked after
I have all the info and want to create the records on the
table "tbllotawardbreakout" (the table with savings by
month)

Private Sub cmdCalcMonthlySavings_Click()

Dim curSavingsByMonth As Currency
Dim intMonth As Integer
Dim rstTable As Recordset
Dim dbsScorecard As Database
Dim x As Integer

Set dbsScorecard = OpenDatabase("E-Scorecard.mdb")

Set rstTable = _
dbsScorecard.OpenRecordset("tblLotAwardBreakout",
dbOpenDynaset)

x = 1
intMonth = [ContractStart].Value
curSavingsByMonth = [AwardBidAmt].Value /
[ContractLength].Value

For x = 1 To [ContractLength].Value
'calculate the monthly savings and add records
'to the subform Brandon Cheal 7/14/04

With rstTable
.AddNew
!QSourceProjectID = "1145"
!LotNumber = "1"
!DivisionID = "1"
!LocationID = "1"
!SavingsMonth = intMonth
!Savings = curSavingsByMonth
.Update
.Bookmark = .LastModified
End With

intMonth = intMonth + 1
Next x


End Sub
 
Top