Calculating accumalative fields

[Anthony]

I currently have a field which calculates the age of stock
over a period of time based on how much inventory I have.
Currently I am calculating each line separately and this
is ok, but I would like to match up similar data. For
example, if I have 20 units that are 60 days old, and 15
units that are 45 days old the results would be as follows.

* This is a predetermined value for another field

QTY AGE FORMULA RESULTS (days)
20 60 ((20/80) x 170*) + AGE) 102.5
15 45 ((15/80) x 170*) + AGE) 76.8
45 12 ((12/80) x 170*) + AGE) 37.5

The problem here is that this does not take into account
the time it takes to sell the older stuff first. What I
would like to happen is some adding if the Age is less on
the same item. Example

QTY AGE FORMULA RESULTS (days)
20 60 ((20/80) x 170*) + AGE) 102.5
15 45 ((15+20/80) x 170*) + AGE) 119.4
45 12 ((12+15+45/80) x 170*) + AGE) 182


If you have any idea how this can be it would be most
appreciated. Should you require any more information,
please contact me via email at

(e-mail address removed)


Thankyou

 

[[MVP] S.Clark]

I think the "Matching up similar data" that you desire will come in the form
of the GROUP BY clause. I think that you would need to aggregate the values
ahead of time, meaning before the final number is placed within the
calculation.

I don't grasp from your example, what it is that you use for your
grouping(i.e. ProductID, ProductCategoryID, Color, Size, etc), so I can't
really give you a more specific answer right now.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-