Bills of Materials / Inventory

[CRM001]

Hi all,
I am new to Access so I'm just learning.
I downloaded a template called Inventory which I believe it is what we need but with a few mods. One of them is I need to be able to create BOMs and calculate Items available for those BOMs. This is what I did so far:
1) I created a Table called "Components" with a Parent ID, Component ID and a "Qty req'd for BOM" field.
2) I created a query to link the Inventory Table (with Items) and the "Components" table and try to (unsuccessfully) add a field for current stock levels of the items. THis field is already part of a report.



Now, I am trying to add a field in the query to calculate the # of BOMs I could create by Item by trying to create a field in the query with a field called "Stock Level" from a report and the "Qty Req'd field" from my components table. I tried using the Exp builder but once I create the expression and tab out of the field (query design view) the exp dissapears fm the query.

Thanks for any input you can provide.
CRM

[Robertt]

CRM,

Can you post the formula you are trying use that is disappearing? That would be a lot easier for us to look at rather than guessing as to what you need to do.

Robert

[CRM001]

CRM,

Can you post the formula you are trying use that is disappearing? That would be a lot easier for us to look at rather than guessing as to what you need to do.

Robert

Thanks Robert,
The formula would be
BOM available = MIN (each of the components current "Stock level" divided by the "Qty req'd field").
Ex:
BOM X is made out of: 3 Comp A and 5 Comp B.
Current stock of A = 6
Current stock of B = 5
=> BOM X available = Min (6/3 for prod A = 2; 5/5 for prod B = 1) => BOM X available = 1

I hope this clarifies, and please if you are going to get business from us, then never mind, I got a couple of Indian guys trying to "HELP" at a charge of $75.00 an hour. I am not for that, but if this is how this forum works, I'd be getting help somewhere else.
Thanks again,
CRM

[Robertt]

CRM,

I wouldn't look at free help with such contempt, especially since my hourly rate is much higher than your other offers:-) These forums are a give and take, sometimes we ask questions, other times we answer them - for free.

Since I don't know your database structure, let me ask if your values for current stock are from a single record, or is it a cummulative total? I.e., can we just query an existing column to get the qty or do we have to pre calculate that first?

If the values for your stock are already subtotal, then you should be able to do something similar to...

select bom:MIN ([Stock level]/[Qty req'd field])...

If the value needs to be pre calculated, then

select bomselect sum([qtypurchased]) -sum([qtySold]) as stockLevel from myOrders where myOrders.productid=myTable.productid)/[qty req'd field] from mytable where...

This latter is pretty challenging to do in the quey builder, but can be done fairly easily in the SQL window.

HTH,

Robert

[TheShabz]

Kudos to you Robert for even answering. I dont see how anything implied any "business" transaction occuring.