Hopefully SQL can handle processing alternative part numbers?

[geolemon]

I'm building a small-scale MRP system for an electronics manufacturing business. There are assemblies that are built, and parts that go into those assemblies. The individual component parts that go into each assembly have their own component part numbers, and since many manufacturers make those basic components, often times alternative part numbers (and quantities) exist in the inventory system for a given component part.

Fortunately, we assign internal part numbers, so there's a one-to-many relationship between the internal part numbers that we use to build assemblies by and the actual manufacturer's part numbers that we've purchased to maintain inventory for that internal part number.

So, we could have more than one MfgPN for any given internal PN (for example):

Internal PN | Manufacturer PN | Quantity
RES-000293-R | CRCW0603330RJNEA | 150
RES-000293-R | RM06J301CT-ND-TR | 5000

So, in other words, either of those MfgPN's is completely appropriate to use wherever the assembly calls out RES-000293-R.

And our operators will use up existing, opened reels of parts before mounting new reels. So, in this example, we'll use up the stock of 150 CRCW0603330RJNEA before mounting the unopened reel of RM06J301CT-ND-TR.
Parts don't have expiration dates, so we use up small quantities before moving on to larger ones.

The dillema this creates is in creating SQL code to decrement inventory appropriately. Consider building 500 assemblies where this part is used once per assembly:
In reality, we'd use up the 150 first, then use 350 of the RM06J301CT-ND-TR.
So, after running, I'd have this:

Internal PN | Manufacturer PN | Quantity
RES-000293-R | CRCW0603330RJNEA | 0
RES-000293-R | RM06J301CT-ND-TR | 4650

From a SQL standpoint, I have:
Inventory table has MfgPN and Qty.
Parts table has IntPN and MfgPN.
Assemblies table has data on each assembly location, including IntPN.

I created a stored query (used as a view) to provide IntPN, count(*) for any given assembly, so I know how many IntPN's I need to decrement from inventory.

How could I then process that against [functionally] potentially multiple instances of that IntPN in inventory, so that it subtracts from the smallest inventory item first, then the next smallest, and so on until the full decrement quantity has been reached?

I'd like to stay within SQL if possible to do this in a query.

[Stewart Ross]

Hi Geolemon. You do not mention the name of the primary key of your parts table; it is either a field you haven't told us about so far, or a compound key based on internal PN and manufacturers PN. We need to know the PK so that it is possible to identify uniquely a row to update, by joining back your quantity-finding query to the table. If you could advise what field this is it would be helpful.

Also note that it is potentially possible to have equal quantities of parts in stock from more than one manufacturer. This can be dealt with, for example by selecting just the one row arbitrarily, but as you may not yet have considered this possibility you need to decide what to do in those circumstances.

However, I do not think it is possible in SQL to decrement partial totals across rows as you are suggesting (i.e. if amount > what is in stock, subtract all from what is in stock and decrement remainder for next item). It is most likely that a code-based loop solution would be much simpler to achieve, and indeed may be the only feasible way to accomplish what you ask.

-Stewart

[geolemon]

Primary key is MfgPN in the parts table, since it has a many-to-one relationship to CustPN. In that table, CustPN is just an attribute associated with MfgPN because of that relationship.

I think what I'm going to do is create two stored queries...
--- One to identify the min Qty and min MfgPN (since I at least need it returned but need the 'group' by to work) where Qty>0
---The second to identify the same thing, where Qty <0.

I can then process my decrementing against the first view, ensuring I have only one CustPN, and it's the one that we have the lowest Qty of all CustPN's.

If that particular MfgPN doesn't have enough parts, it'll turn into a negative number in my inventory.

I can then repeatedly process querying the second view to find negative numbers, and using those numbers (multiplied by -1) to process against the first view again, iterating until I no longer have any negative numbers in my Inventory table. And I'll probably need a temporary table to track the numbers I find as negatives so I can zero just those after each iteration...

Sound reasonable? It keeps me in queryland, other than that I'll need some VBA to control the looping.

[Stewart Ross]

Hi. I'd go the code solution - I do not think you will find it feasible to do what you suggest in SQL. Good luck if you want to try it, however.

Unless there is something else in your parts table that you haven't told us about there seems to be little reason for having a separate table. There is a 1-1 relationship between the inventory table and the parts table, and the norm when faced with this is to merge the two together. In this case, it is just transferring your IntPN field into your inventory table - which is indeed what you show in post 1 from your view. You would not then have to maintain two tables with manufacturers part numbers in your application.

I will come back to you after investigating a code solution.

-Stewart

PS you are referring to CustPN in post 3 - I presume this is a synonym for IntPN?

[Stewart Ross]

Here is a VBA function which implements a selective decrement on the stock items. It uses an internal SQL statement based on the parts and inventory tables, which as I mentioned in my previous post I think can be simplified if the inventory table is altered to contain the internal part number.

Public Function fDecrementInventory(ByVal strIntPN As String, ByVal lngQty As Long)
    'Decrements the stock quantities in the Inventory table where the
    'internal part number matches the argument provided
    'If the quantity concerned is greater than any single amount in stock
    'the stock quantity is decremented across multiple stock items until the
    'total quantity has been accounted for.
    '
    'Assumptions:
    ' 1. the internal part number does exist already in the dataset
    ' 2. there is sufficient stock in hand to issue the quantity specified
    '
    Dim strSQL As String
    Dim lngQtyInStock As Long
    Dim RS As DAO.Recordset
    strSQL = "SELECT Parts.IntPN, Inventory.MfgPN, Inventory.Qty"
    strSQL = strSQL & " FROM Inventory INNER JOIN Parts ON Inventory.MfgPN = Parts.MfgPN"
    strSQL = strSQL & " WHERE IntPN = '" & strIntPN & "'"
    strSQL = strSQL & " ORDER BY Parts.IntPN, Inventory.Qty;"
    Set RS = CurrentDb.OpenRecordset(strSQL)
    Do While Not RS.EOF And lngQty > 0
        RS.Edit
        lngQtyInStock = RS!Qty
        If lngQtyInStock >= lngQty Then
            RS!Qty = lngQtyInStock - lngQty
            lngQty = 0
        Else
            RS!Qty = 0
            lngQty = lngQty - lngQtyInStock
        End If
        RS.Update
        RS.MoveNext
    Loop
    RS.Close
End Function

This function requires that a reference to the DAO object library is set; if it fails to compile, from the VB editor select Tools, References and tick Microsoft DAO 3.6 object library (or later).

Sample function call in VBA code, from command button or similar (replace the arguments with suitable variables or form field references):

Call fDecrementInventory (internal_partnumber, qty_issued)

Tested in VB immediate window like this:

? fDecrementInventory("RES-000293-R", 500)

Test Data

Before
IntPN         MfgPN              Qty
RES-000293-R  RM06J301CT-ND-TR   150
RES-000293-R  CRCW0603330RJNEA  5000
RES-000293-R  ABCDEFG-1234      6000
RES-000293-S  12345-FGH          100
RES-000293-S  12345-ABCDEFG      100
 
After
IntPN         MfgPN              Qty
RES-000293-R  RM06J301CT-ND-TR     0
RES-000293-R  CRCW0603330RJNEA  4650
RES-000293-R  ABCDEFG-1234      6000
RES-000293-S  12345-FGH          100
RES-000293-S  12345-ABCDEFG      100

-Stewart

[geolemon]

I appreciate it -

There isn't actually a 1:1 relationship... I just described the scenario simplistically trying to keep the thread simple and the issue spotlighted.

We're actually a contract manufacturer serving many customers - you could substitute "customer part number" for "internal part number", and the inventory table reflects what actual, real, physical parts we actually have in inventory (by Manufacturer part number), where the Parts table tracks what parts we are allowed to have for any given Customer Part Number - including the possibility of approved alternate or substitute Manufacturer Part Numbers. In many cases, the same Manufacturer Part Number in inventory is common (if not actually physically shared) across multiple products, if not multiple customers.

If it weren't, I'd love to get rid of the MfgPN altogether and just run on CustPN!

I'm going to check out the code you put up here, I think there's a lot I can use there -thanks!

I'll probably post what I end up with.