Counting inventory

[Charity]

I have old excel files with figures I need to figure out.  They look like this

 

Date - Item# - number in stock

 

These are inventory takes over periods of time.  I have imported them all (over 360 files) into FM and I need to find out per year the amount sold per item.  I have tried sub-summary with item# and I have found the year records I want.  But how do I subtract the starting amount from the ending amount in this report?  Do I need calculation with another relationship?

 

The lowest date of each item would be the beginning inventory and the last date the ending.  I sorted by Item number and then date but I still do not know what to do next.

[Wim Decorte]

I don't think you can get that number from that.  # in stock will go down with items sold but will go up with items purchased.  If there is only one entry per day you will not be able to determine how many were sold.

 

For instance between two dates the # in stock can go up by 5 but really mean you sold 10 and bought 15, or sold 100 and bought 105...

 

What you need are the individual transactions that affect the # in stock

[Charity]

This is an overstock warehouse which was liquidated. No purchases came in.  It was in a program called Stock_Tracker_Plus written in 1985 in Access.  I am trying to find out how much was sold per month or year at least so I can bring those items and their sales into the new parts system.  So if it starts with 25 Master Cylinders in all the spreadsheets for the first date which are now in one table and the last date says 20, I can say there were 5 sold that year.  I want by month but if the best I can get is by year I will accept that also since it will be within the same tax year at least.

 

Surplus government stock is sometimes purchased and stashed in warehouses and then sold until gone or resold to other suppliers.  Thank you Wim and I hope that makes more sense now.  I do not have access to the accounting program that held the actual sales records.

[Wim Decorte]

This is an overstock warehouse which was liquidated. No purchases came in. 

 

Would have made sense to mention that from the get-go

 

All of this data is static so you don't need calculated fields for this, all you need is one looping script acting on the data sorted in the way you want it to (by monty & product or by product & by month).  Either way one script can collect the totals and write them off to a new summary table.

[Charity]

Yes I should have been more clear. I forgot.  I did not mean to mess up, sorry and I will do better next time.  I got the script to work.  I am so happy with myself when I figure something out.  It is like solving a puzzle.  No it is more like winning a gold metal especially when it was very difficult which this was and most of this is.  Thank you again Wim.