Calculate Percentage based on Age

[KCK]

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

 

[DCrake]

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

 

[sportsguy]

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

 

[SOS]

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:

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

 

[SOS]

And I would use DATE and not NOW when doing date comparisons, unless you NEED the time piece in there.