By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,832 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

Hopefully SQL can handle processing alternative part numbers?

geolemon
P: 39
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.
Feb 26 '09 #1
Share this Question
Share on Google+
5 Replies


Expert Mod 2.5K+
P: 2,545
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
Feb 26 '09 #2

geolemon
P: 39
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.
Feb 26 '09 #3

Expert Mod 2.5K+
P: 2,545
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?
Feb 26 '09 #4

Expert Mod 2.5K+
P: 2,545
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.

Expand|Select|Wrap|Line Numbers
  1. Public Function fDecrementInventory(ByVal strIntPN As String, ByVal lngQty As Long)
  2.     'Decrements the stock quantities in the Inventory table where the
  3.     'internal part number matches the argument provided
  4.     'If the quantity concerned is greater than any single amount in stock
  5.     'the stock quantity is decremented across multiple stock items until the
  6.     'total quantity has been accounted for.
  7.     '
  8.     'Assumptions:
  9.     ' 1. the internal part number does exist already in the dataset
  10.     ' 2. there is sufficient stock in hand to issue the quantity specified
  11.     '
  12.     Dim strSQL As String
  13.     Dim lngQtyInStock As Long
  14.     Dim RS As DAO.Recordset
  15.     strSQL = "SELECT Parts.IntPN, Inventory.MfgPN, Inventory.Qty"
  16.     strSQL = strSQL & " FROM Inventory INNER JOIN Parts ON Inventory.MfgPN = Parts.MfgPN"
  17.     strSQL = strSQL & " WHERE IntPN = '" & strIntPN & "'"
  18.     strSQL = strSQL & " ORDER BY Parts.IntPN, Inventory.Qty;"
  19.     Set RS = CurrentDb.OpenRecordset(strSQL)
  20.     Do While Not RS.EOF And lngQty > 0
  21.         RS.Edit
  22.         lngQtyInStock = RS!Qty
  23.         If lngQtyInStock >= lngQty Then
  24.             RS!Qty = lngQtyInStock - lngQty
  25.             lngQty = 0
  26.         Else
  27.             RS!Qty = 0
  28.             lngQty = lngQty - lngQtyInStock
  29.         End If
  30.         RS.Update
  31.         RS.MoveNext
  32.     Loop
  33.     RS.Close
  34. 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
Expand|Select|Wrap|Line Numbers
  1. Before
  2. IntPN         MfgPN              Qty
  3. RES-000293-R  RM06J301CT-ND-TR   150
  4. RES-000293-R  CRCW0603330RJNEA  5000
  5. RES-000293-R  ABCDEFG-1234      6000
  6. RES-000293-S  12345-FGH          100
  7. RES-000293-S  12345-ABCDEFG      100
  8.  
  9. After
  10. IntPN         MfgPN              Qty
  11. RES-000293-R  RM06J301CT-ND-TR     0
  12. RES-000293-R  CRCW0603330RJNEA  4650
  13. RES-000293-R  ABCDEFG-1234      6000
  14. RES-000293-S  12345-FGH          100
  15. RES-000293-S  12345-ABCDEFG      100
-Stewart
Feb 26 '09 #5

geolemon
P: 39
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.
Feb 26 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.