Calculate Percentage based on Age

KCK

Registered User.
Local time
Yesterday, 22:19
Joined
Aug 18, 2006
Messages
37
I have a process in Excel that I need to migrate to an Access2003 database.
We need to calclulate a percentage of the value of a Part# based on how long it has been in Inventory.
The table I have contains GroupType, Part#, Value, ReceiptDate.
In excel I have a matrix with 6 GROUPNAMES down the left hand side, Age buckets (0-45, 46-90, 91-120, etc. across the top and the percentage where the two coordinates meet.
Example:
Wdget1 0-45days=100% of value, 46-90days=75% of value, 91-120days = 50% of value, etc.
If Widget1 value is $100 at the ReceiptDate, at 46 days the value ($100) is multiplied by 75% to show a new value of $75, at 91 days the value ($100) is multiplied by 50% to show a new value of $50, etc.
All this will ultimately be placed on a report grouped by GROUPNAME and AGE.

I think I need a table containing the GROUPNAMES, Percentage and Age but am not sure this is the way to go or how it should look.

Can someone point me in the right direction?
Thanks,
Kerry
 
OTTOMH.

in order to perform a caluculation you need a minium of two entities the Numerator and the Denominator from your example I can't identify the former.

If you explain it using field names or can post the excel sheet things may become clearer.

David
 
An easier way is to create a function which calculates the date difference, and select the correct multiplier

Function MyFunction(anyStartDate as Date) as Currency

If DateDiff(Now(),anyStartDate) < =45 then
MyFunction = 1
ElseIf DateDiff(Now(),anyStartDate) < =90 then
MyFunction = 0.75
. . . . .

End if

End function
 
An easier way is to create a function which calculates the date difference, and select the correct multiplier

Function MyFunction(anyStartDate as Date) as Currency

If DateDiff(Now(),anyStartDate) < =45 then
MyFunction = 1
ElseIf DateDiff(Now(),anyStartDate) < =90 then
MyFunction = 0.75
. . . . .

End if

End function
Sportsguy -

A more efficient function would use the Select Case Statement:

Code:
Function MyFunction(anyStartDate as Date) as Currency
 
Select Case DateDiff("d", Date, anyStartDate)
   Case <=45
      MyFunction = 1
   Case <=90
      MyFunction = .75
   Case <=120
      MyFunction = .5
   Case Else
      MyFunction = .25
End Select
End Function
 
And I would use DATE and not NOW when doing date comparisons, unless you NEED the time piece in there.
 

Users who are viewing this thread

Back
Top Bottom