creating BOM (1 Viewer)

[mabino79]

Dear all

I need to create a Bill Of Material (BOM) in MS access but there is some issue want to discuss first.

The product will be created with an Assembly of 5 accessories, there will be two branches with the same product code but when we stock out or send it to manufacturing the stock should be deducted from the main branch first if there will be no stock in the main branch then it deducts from the sub-branch.

Can anyone help on this or make any db earlier, please share for reference.

Thanks in Advance

 

[MajP]

See if any of this usable

Looking for some direction.

I am realizing that the Tree View works well for navigation, but would not be our preferred method for input/queries. Too many mouse clicks. That depends on your data. I can demo some structures that adding a node and moving a node would be way faster then any other method. Treeviews work well...

www.access-programmers.co.uk

 

[mabino79]

no, I have a simple issue to solve

5 accessory combinations are to be deducted only from the preferred branch first.

 

[MajP]

5 accessory combinations are to be deducted only from the preferred branch first.

That may seem logical to you and mean something, but I think for most of us not knowing your process we are clueless in what that means.
You likely need to draw this out, similar to the OP in the thread I posted. In relation to a database and table structure I have no idea what those terms mean. Do you already have a table structure that supports your BOM yet? In the posted thread @Pat Hartman and I discuss two table structures that support a BOM. If you already have something proposed, please post the DB.

 

[mabino79]

I just want to know how to set the priority to make a deduction from stock on the priority of the specified branch first then second branch, as the product is the same

for example, i have 100 pcs in Branch A & 50 pcs in Branch B, how can we deduct the qty of 60 pcs preferably set to Main Branch B as the stock is cumulative

rest is clear.

 

[mabino79]

I just want to know how to set the priority to make a deduction from stock on the priority of the specified branch first then second branch, as the product is the same

for example, i have 100 pcs in Branch A & 50 pcs in Branch B, how can we deduct the qty of 60 pcs preferably set to Main Branch B as the stock is cumulative

rest is clear.

have made as above just to show the ordering ,assembling & input of products, now I need to deduct the product as a sale

 

[Gasman]

Hold the preferred branch in the parts record?

You appear to be movng the goalposts, as your first post wanted main, then sub branch, now you want a preferred branch, which might be the other way around.

 

[mabino79]

yes, its required to be deducted from specified branch first.

 

[Gasman]

So store which branch in the part record source.

 

[mabino79]

So store which branch in the part record source.

stock will be cumulitive total of all branches

 

[Gasman]

stock will be cumulitive total of all branches

So what, all you are storing is the preferred branch to start taking stock from?

 

[jdraw]

Where is Branch, or more specifically Branch A and/or Branch B in you diagram in #6?
Where are Sales or is that equivalent to a CompletedOrder?

Your terminology is not helping with communication as MajP said in #4. It might be helpful if you gave a concrete example or 2 describing what an Order is for, what constitutes a Product, what "Accessory(s)" are needed, where they come from, where Branch fits, and how you manage inventory(deduct Product and accessory(s) when a sale occurs. Use a simple analogy if possible --anything to clarify the underlying requirement.

 

[MajP]

Good to see that I am not the only one completely clueless in what the OP is saying. Also this naming convention is very bad and would confuse anyone. You have the Project in Order and Input and should only be in one place. Terms like Qty, Unit, Project, Description are all used in multiple tables and probably have some very different meanings.

 

[Mike Krailo]

Setting priority is done by selecting the priority you want based on whatever rules you wish to use. Do you wish to deplete the entire Branch B inventory first and only after it is depleted, start taking from Branch A? You need to calculate the total needed from each Branch based on the amount needed. Assuming you have a function to calculate total available for each branch then something like this may work. All air code, so with out details, just giving a rough idea how to solve.

TotAvailableBranchA = CalcTotRemaining("A")
TotAvailableBranchB = CalcTotRemaining("B")

' See if needed qty exceeds available in preferred BranchB
Private Function IsExceeded(TotQtyDesired as long, Optional BranchBAvailable as String = "A") As Boolean
    If TotQtyDesired > TotAvailableBranchB Then
        IsExceeded = True
    Else
        IsExceeded = False
    End IF
End Function

' Now in main transaction that is taking pieces use this simple logic within your code
Dim PreferredBranchDepleted as Boolean
PreferredBranchDepleted = False

If IsExceeded Then
    Dim i as Long
    Dim RemainingBranchA as Long
    ' Set flag
    PreferredBranchDepleted = True
    RemainingBranchA = TotQtyDesired - TotAvailableBranchB
    ' Take all that is remaining from preferred BranchB
    for i = 1 to TotAvailableBranchB
        ' Code to process this portion of the transaction here or simply subtract the full amount
    Next i
    
    i = 1
    for i = 1 to RemainingBranchA
        ' Code to process the remaining amount from BranchA
    Next i
Else
    ' Do your normal transaction all against BranchB only
End If

 

[Pat Hartman]

Will you ALWAYS draw from branch A before branch B for ALL components? If so, Add a priority field to your branch table and sort your inventory records on that as you go into the inteory allocation part. If the priority for a particular assembly/part depends on the assembly/part, then you need to add a field to the part table to identify the prefered branch. This will be a more complicated process.

 

[Mike Krailo]

for example, i have 100 pcs in Branch A & 50 pcs in Branch B, how can we deduct the qty of 60 pcs preferably set to Main Branch B as the stock is cumulative

In his example he defined Branch B as the preferred branch. His example, not mine.

 

[mabino79]

5 accessories make a product that will be stocked in & out but the requirement is to keep the stock of the Accessory, not the main part we make to be stock out. when we out the stock it will be deducted from the Branch "specified" first. that is only the requirement.

for Eg. Accessory -1, Accessory -3, Accessory -5, Accessory -8 & Accessory -10 makes a Product 1

stock in : branch A - 100 Branch B = 200 (Preffered)

stock out now required 250 product 1, so it will be deduct first from Branch B, then Branch A.

accordingly the accessories used to make product 1 will be deducted

 

[jdraw]

mabino79,
This is my understanding of your posts: (Please adjust as needed)

Overview:
You make many Products for Sale (P1...P99)
A Product is composed of 1 or more Accessories (P1...A1,A3,A5,A8 and A10)
There are many Accessories
Each Accessory is identified by an AccessoryType (A1...A99)
Each Accessory is supplied by Branch A or Branch B
Branch B is the preferred Supplier
Only when Branch B has no available supply of an Accessory, get accessory from Branch A

---------------------------------------------

You have a list of Products to be made
Each Product has a list of components(Accessories) required
Available supply of each Accessory is known for each Supplier (BranchA, BranchB)
You can calculate quantities of each Accessory required
You can calculate quantities required vs supply at BranchB to identify when supply will be exhausted
You can then determine quantity of Accessory(s) to be supplied via BranchA

review Posts #14 and #15

 

[Pat Hartman]

I just want to know how to set the priority to make a deduction from stock on the priority of the specified branch first

I told you how to do this back in #15. You never answered my question regarding the rules though.

 

[Gasman]

I told you how to do this back in #15. You never answered my question regarding the rules though.

And I said the same in #9