Access should be able to manage stock control far better than Excel ever could, but if you think Excel is the better choice, good luck.
I haven't been involved in the other part of your attempts to find your answers because I've been sick and had to travel due to Hurricane Ivan. (I'm in south Louisiana.) But I'm going to make a couple of comments that are intended to be helpful. Unfortunately, they could be taken as a personal criticism, which is NOT my intention. But sometimes you have to say the things that need to be said.
If you honestly think that Excel can do this and Access can't, you betray a certain mind-set. Flat-file thinking will limit and hamper your designs every time, because the two programs are not the same and never will be the same. The problem for beginners in Access is that datasheet views look SO much like an Excel spreadsheet that they simply MUST be the same, right? Wrong. Excel, well,... excels in dealing with certain types of data layout for visual presentation and it includes formulas, but eventually you will reach a limit with it and you will tear your hair out wholesale. Access has much higher limits and greater flexibility, but you have to leave two-dimensional (spreadsheet) thinking behind. In design terms, this is a quantum leap in modes of thought.
My advice to folks planning to stand up ANYTHING complex in Access is always the same. First, get a dry-erase board and markers, a box (not a pad - a BOX) of sticky notes and some pens. Now start identifying elements of your business. You talk about stock control. OK, I'll generalize a bit, assuming this is a sales case (but it also works for a manufacturing situation, just another wrinkle or two.)
You have inventory items. These items have descriptions. They enter your inventory through transactions that add stock - incoming shipments, returns, stock level corrections (after a miscount, e.g.). They leave your inventory through transactions that remove stock - sales, shrinkage (defects, theft, damage, miscounts). Your suppliers can provide the items. Each supplier can perhaps provide different items, maybe with some overlap. Your customers can purchase your stock. (In the manufacturing case, one of your other departments is your customer and your customer table gets EASY.)
OK, what I have done in the above paragraph is identified some elements of your functional business model. WITHOUT THIS MODEL (tailored for your real business), PACK IT UP AND GO HOME. You will NEVER get Access to help you. But for that matter, Excel won't give you much more help, either.
Now, on your dry board, identify some tables that describe your stock items. Identify your suppliers. Identify your customers. You should have a table of stock descriptions. A table of suppliers. A table of customers. The sticky notes will be used to represent individual records in a table.
Next, you have to decide how these business entities relate to each other. Each supplier has a different list of things that company can supply. The sum of those lists MUST cover every item in your item description list. You don't need to duplicate the list for each supplier, though. You can make what is called a linking table, which lists one item identifier number and the supplier number of the company from which you can get it. It is fair game to have multiple entries per supplier AND multiple entries per item, but you would never have duplicated records in which you have the same exact supplier and item combination in this list twice.
So on the board, you have a few stickies for each supplier and for each item. You can then make stickies for each supply source combination. Draw lines between the stickies to help you see how that relationship works.
OK, when you order items, the supplier supplies them. When the items come on board, you have a bill of lading that should report the number of each item you have received. This is the start of your "real" inventory. Your bill of lading becomes an entry in a table, and the line-items of that bill become entries in a child table. They are your inventory-add detail records. Again, make some stickies for each bill of lading, and make some more stickies for each line item in that bill. Draw lines to see how it works.
When you sell some items, your customer has a detailed purchase order, and the details report the number of each item sold. This is another part of your "real" inventory. The PO becomes an entry in a table. The line-items in each PO become entries in a child table. They are your inventory-remove detail records. Again, the stickies will help you SEE the relationship. I am talking visualization aid, nothing more, but it is a way to help you break out of the two-dimensional thinking mode. And that is crucial.
By now you should have a messy dry-board, but it has enough for you to see the relation between inventory and add/remove transactions VISUALLY. The SUM of adds and removes for a part number tell you how many items you have on hand or how many items you need to order, or whatever. Detailed stock return and shrinkage transactions also contribute to the stock item counts. You can have each of these transaction types in a separate table and just use something called a UNION query to form the totals.
Why did I send you down this path? Because in order to program it, you have to SEE it with your mind's eye. If you are thinking along Excel lines, you tell me you cannot see your model. And that is what I hope to help you to see. 'cause without the model, you are dead in the water.
