These two fields amuse me to no end: [Available Inventory], [Actual Avail Inventory]. Reminds me of my first week at a lot of my jobs where I was to move them from Excel to Access. Tell me the production spreadsheet you use is called "ProductionFile_FinalVersion2_Copy3_B.xls" and I can tell you exactly where you work because I was formerly employed there.
That was the criticism part, here's the constructive--read up on normalization. That's the process of setting up your tables properly. Inv_008_Inventory_Summary has a few errors in it:
1. Storing data in field names. Inv_008_Inventory_Summary is essentially a status table, but instead of having a field to hold the status of each quantity, you are storing the status in the field name. Instead all the statuses should be a value in a field in a new table. I would make a new table called "InventoryStatusQuantities" with this structure:
InventoryStatusQuantities
iq_ID, autonumber, primary key of table
ID_Component, number, holds the ComponentID value in Inv_008_Inventory_Summary
iq_Qty, number, the quantity of the component in this status
iq_Status, text, this will all the status values currently the names of fields (Available Inventory, On Hold, In Process, etc.)
That's it. Then you move your quantities from Inv_008_Summary to that new table--one record per quantity.
2. Storing calculated values in a table. I am sure [Total Qty after In-Process Comp] is the sum of the 2 [..In-Process...] fields. With that new InventoryStatusQuantites you run a query to get that value instead of storing it. The same for any other calculable value--you don't store it in a table, you calculate it in a query.
3. You have both [ProdSubLineId] and [ProdLineID] in tblProducts, only [ProdSubLineID] should be in there. I am assuming you have a ProdSubLine table where one of the fields in it is what ProdLine that Subline belongs to. If that's true, you don't store the ProdLineID in tblProducts because you can get there just by knowing its Subline.
Those are just the 3 big things I see with that small glimpse into 2.5 of your tables. I am very sure you've commited the same type of errors in the parts I haven't seen. I would read up on normalization, give it a shot with your tables, then complete the Relationship Tool in Access, expand all the tables in it to show all your fields, take a screenshot of it and post it back. That way we can help you get this thing structured correctly and you aren't building a huge undertaking on a poorly structured foundation.