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