Trying to perform a stock-take of sorts
I have just been asked to set up a system that will allow one department to calculate how much of their stock they have sent out to customers.
The way things are currently set up is as follows:
Each order has multiple records, each with an item identifier and the quantity desired, there is another table that has a record for each item that is dealt with, along with the number of items currently in stock, a count of how many have been sent and a count of the total number of the number currently in stock and how many have been sent out.
I am trying to get my head around how to calculate the number of items in stock by querying the orders table and summing up how many of each item have been sent out.
I am more than willing to admit that I have no real experience with FileMaker or database tools, except for what I studied as part of my degree a few years ago.
Any advice, hints and tips would be greatly appreciated.