Automated marking off stock levels

[bbdsbd]

Hello, we are creating a new stock control system in access converting our old excel system but we are having problems with certain mechanics.
Our system currently looks at a table with orders in and finds the product code. The occurrence of the product code in the orders table is then counted to give a sum of sales for each individual product.
We would like to use this count of products to mark off stock components.


We sell Canvas art online, so for us our ‘components’ are panels (a wooden frame with canvas stretched round). When an order comes in, the product code is matched with the product details in another table which holds the sizes of each frame panel in that product(a product can consist of up to 10 panels). So for example an MP07 has one 40x20 panel, two 30x20 panels and two 20x20 panels.

So our product code has a relationship with the panel size At this milestone we need way of counting the occurrence of a size code in the panel columns.
To simplify
We would import orders like this.

We would like to know how many of each type of panel needs to be marked off. So for the first three sales we have two 40x40’s, two 75x50’s, and 4 30x30’s. we can then use the number and sizes of Panels to find out how much raw material is used E.G what size frames need to be cut from the wood and what length of canvas will be used for the print.
What would be best way to record how many of each panel has been used in our system?

[ItsMe]

I recommend revisiting your table structure before you continue. It seems you are "committing spreadsheet" and your data is not "Normalized". You will always have trouble creating queries and maintain your data if your tables do not follow the rules of normalization. Perhaps you can start by creating an ERD and also work through various tutorials on Relational Database.
https://www.youtube.com/watch?v=-fQ-bRllhXc