materail Optimizer

[cavscout]

I have a series of queries that take in part numbers for stocklength materials, qty and cut size needed. It process a Bill of Materials that tells our shop what to fabricate. I am trying to determine how much stock material to pull from inventory to give to the shop to be cut.

I am able to determine how much material is required for each record (if I need 3 pcs @ 10' from a 20' stick, i'll need 2 sticks from inventory). This would also leave me 10' of usable material.

The trick is looking at multiple records to determine if I can utilize the leftover drop from a previous cut to fill a need on another (ie. if I need 1 pce @ 10' from a 20' stick, and also need 2 pcs at 2' from the same stick I should only need 1 stick). My querie treats this as needing 2 sticks because it doesn't realize there is 10' of leftover drop from the previous cut.

Any ideas out there?

 

[pdx_man]

How do you want to group these multiple records? Do you do a daily inventory pull? Weekly? 1 for each purchase order?

Let's just say you do it by day. Add all of the pieces on all of the BOMs and then divide by 20.

If you have multiple cuts , do you have to worry about the kerf? That may not be material enough to worry about, but I'm just wondering if you need 40 6" pieces, a 20' board aint gonna do it.

 

[cavscout]

Thanks pdx_man,

I'm not worried about the kerf. The 20' stick is actually 20'3".

I guess the problem with just doing division by 20 is that you won't always get a clean whole number. ie. if I need
1 @ 20
1 @ 20
1 @ 18
1 @ 15
1 @ 10
1 @ 8
1 @ 7
1 @ 6
1 @ 6
1 @ 6
1 @ 2

the total length needed is 118'. 118/20 = 5.9 or 6. I will actually need 7 sticks to make these lengths. So my first thought is, "OK, I'll just add "2" to the Integer of 5.9. But this won't work every time. If I add 3 more 2' pcs to the BOM to make the overall length 124' we get 6.2 or 7 then add the "2" we end up with 9. I still only need 7.

If I missed something in your solution I'm sorry, please drop that load of bricks on my head.

Thanks,

 

[pdx_man]

Ah, yes. You are going to have quite a time, here. Take a look at this thread:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=51209

You are going to have to find create a list of all item and then get the combinations where you get the most number of items meeting the criteria <= 20 and then remove those items from the list and repeat until you have no items on the list. It is not going to be easy to do, but start with the thread above.

 

[cavscout]

Thanks! There's a lot of good information here. I'll see what I can do with it.