Hope you’re well.
I am stuck on a section of a new project and wondered if you could help please!
- I have two tables - one is a ‘Stock Master List’ which holds the total quantity numbers of items/parts in stock
- The other table is ‘Parts - Jobs’ which is used associated with Jobs the client has to complete. They are linked based on stock quantity and Parts Code.
So what I would like to do is add a column in the Stock Master List which provides with me the latest up to date stock availability at any one time. Simple example below:
There are 4 x Pump’s in the Stock Master List (in stock) and a job starts and they use 3 of those Pump’s, I would like the Stock Master List to know there is only now 1 in available in stock.
I have tried creating a relationship between the ‘Parts Code’ field (which appears in both as a common field) and the creating a ROLL UP column in the Stock Master List but it doesn’t appear to be ‘summing’ correctly as I can only see Count or Count Unique. Is this the correct approach here?
If anyone has any better suggestions, please let me know! If it’s quicker/easier feel free to send a video to save time explaining the logic in writing.