Jump to content
Sign in to follow this  
Charity

Counting inventory

This topic is 1993 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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

  • Like 1

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

This topic is 1993 days old. Please don't post here. Open a new topic instead.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Who Viewed the Topic

    1 member has viewed this topic:
    comment 
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.