Pulling Kit Quantities from Component Quantities Using Junction Table (1 Viewer)

treeman

New member
Local time
Today, 06:43
Joined
Nov 11, 2015
Messages
11
Image 001.jpg


I am trying to duplicate what we have in an excel file in a query in access. Our products are made of usually 2-3 components, essentially a kit. In Access, we already have the Inventory Qty for all components. In the excel file, we are computing the Product Qty using formulas within the cells that essentially take the lowest number of what component qty makes up the product (kit) and that becomes the available Product Qty (see above image)

I think we have all the queries we need to do this, I'm just not sure how to go about it. I've started with this concept with a new query:

Image 002.jpg


1) I have the Inventory Summary which gives me the "Actual Available Inventory" field and those values are the accurate inventory of the component parts.

2) We have Product Component Junction table which has the ID for that table along with the assigned Component and Product IDs (ComponentID & ProdID).

You can see I started this but I'm lost on where to go from here. Any help would be appreciated.

Thanks
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:43
Joined
Sep 12, 2006
Messages
15,614
I think I would have a table of the kit components
(this looks like your table tblProdComponentJunction)

So in your table KIT A comprises
3x part 1234
2 x part 4076
1 x part 2748

then you can compare your current inventory of each of these parts to get a query that looks like this
your current inventory is in Inv_008_Inventory_Summary

the query needs these fields for Kit A

part no required available sets on hand (av/req)
1234 3 78 26
4076 2 56 28
2748 1 48 48

if you need the same part for multiple kits, then you need to decide how to deal with that.
If you need more kits than you have available, then you know what components you need to order/make
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:43
Joined
Feb 19, 2002
Messages
42,981
Select whatever fields you want from the first two tables, Change the query type to Totals. Then from the Product table, you want only Product Qty and change the aggregation from Group By to Min. Do not select ANY other columns from the Product table.
 

treeman

New member
Local time
Today, 06:43
Joined
Nov 11, 2015
Messages
11
I think I would have a table of the kit components
if you need the same part for multiple kits, then you need to decide how to deal with that.
If you need more kits than you have available, then you know what components you need to order/make

Thanks but I already have that table. That's the ProdComponent Junction table you see in the query. It assigns components to each product even if the products share components. I'm not looking for what components we need to order. I just need to translate the already available component quantities ("Actual Available Inventory") into "Available Product Quantities" using the already established relationship of these two IDs in the Junction table.

The component quantiles are already established and the relationship is already established. Somewhere in criteria or expression there just needs to be something about using the number of the lowest value for the qty of the component that makes up the product.
 

treeman

New member
Local time
Today, 06:43
Joined
Nov 11, 2015
Messages
11
Select whatever fields you want from the first two tables, Change the query type to Totals. Then from the Product table, you want only Product Qty and change the aggregation from Group By to Min. Do not select ANY other columns from the Product table.
Thanks, That sounds like a plan worth pursuing. I guess the only question is that there is no qty field in Products. That's what we're trying to establish from the available inventory of the components. Can I create a field with an expression or formula somehow. I do not need to (or probably want to) store that value so it would not go into a table like products.
 

plog

Banishment Pending
Local time
Today, 08:43
Joined
May 11, 2011
Messages
11,613
These two fields amuse me to no end: [Available Inventory], [Actual Avail Inventory]. Reminds me of my first week at a lot of my jobs where I was to move them from Excel to Access. Tell me the production spreadsheet you use is called "ProductionFile_FinalVersion2_Copy3_B.xls" and I can tell you exactly where you work because I was formerly employed there.

That was the criticism part, here's the constructive--read up on normalization. That's the process of setting up your tables properly. Inv_008_Inventory_Summary has a few errors in it:

1. Storing data in field names. Inv_008_Inventory_Summary is essentially a status table, but instead of having a field to hold the status of each quantity, you are storing the status in the field name. Instead all the statuses should be a value in a field in a new table. I would make a new table called "InventoryStatusQuantities" with this structure:

InventoryStatusQuantities
iq_ID, autonumber, primary key of table
ID_Component, number, holds the ComponentID value in Inv_008_Inventory_Summary
iq_Qty, number, the quantity of the component in this status
iq_Status, text, this will all the status values currently the names of fields (Available Inventory, On Hold, In Process, etc.)

That's it. Then you move your quantities from Inv_008_Summary to that new table--one record per quantity.

2. Storing calculated values in a table. I am sure [Total Qty after In-Process Comp] is the sum of the 2 [..In-Process...] fields. With that new InventoryStatusQuantites you run a query to get that value instead of storing it. The same for any other calculable value--you don't store it in a table, you calculate it in a query.

3. You have both [ProdSubLineId] and [ProdLineID] in tblProducts, only [ProdSubLineID] should be in there. I am assuming you have a ProdSubLine table where one of the fields in it is what ProdLine that Subline belongs to. If that's true, you don't store the ProdLineID in tblProducts because you can get there just by knowing its Subline.

Those are just the 3 big things I see with that small glimpse into 2.5 of your tables. I am very sure you've commited the same type of errors in the parts I haven't seen. I would read up on normalization, give it a shot with your tables, then complete the Relationship Tool in Access, expand all the tables in it to show all your fields, take a screenshot of it and post it back. That way we can help you get this thing structured correctly and you aren't building a huge undertaking on a poorly structured foundation.
 

treeman

New member
Local time
Today, 06:43
Joined
Nov 11, 2015
Messages
11
Select whatever fields you want from the first two tables, Change the query type to Totals. Then from the Product table, you want only Product Qty and change the aggregation from Group By to Min. Do not select ANY other columns from the Product table.
Pat,

Thanks for steering me in the right direction. Using your idea I actually was able to get exactly the result I wanted by just changing just a few things shown here. Thanks again!
Image 10.jpg
Image 12.jpg
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:43
Joined
Feb 19, 2002
Messages
42,981
You're welcome. Doesn't mean that plog was wrong. Your table design is a hot mess and really should be fixed.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:43
Joined
Sep 12, 2006
Messages
15,614
Thanks but I already have that table. That's the ProdComponent Junction table you see in the query. It assigns components to each product even if the products share components. I'm not looking for what components we need to order. I just need to translate the already available component quantities ("Actual Available Inventory") into "Available Product Quantities" using the already established relationship of these two IDs in the Junction table.

The component quantiles are already established and the relationship is already established. Somewhere in criteria or expression there just needs to be something about using the number of the lowest value for the qty of the component that makes up the product.

Well you probably need two queries. One, as I said, to evaluate how many kits-worth of stock you have for each of the kit components.
Stock on Hand / Components Required (for each part)

Then a second query to pick the minimum value from this query - or alternatively a form that displays the results in ascending order.
You might be able to get this into a single expression, but sometimes you can't do complicated things with just a single query.

I see you have probably already got to the answers you needed.
 
Last edited:

Users who are viewing this thread

Top Bottom