RE: [MS_AccessPros] Calculating In Stock quantities

I wish you would have provided information about primary and foreign keys. I think this creates a couple 1 to manys from the same table which won't work.

FROM (Parts_Inventory INNER JOIN Inventory_Items_BOM ON
Parts_Inventory.Part_Number=Inventory_Items_BOM.Part_Number) INNER JOIN
Parts_Purchase ON Parts_Inventory.Part_Number=Parts_Purchase.Part_Number;

I think you may need to create totals queries of Inventory_Items_BOM and Parts_Purchase grouped by Part_Number.

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: beader59@gmail.com
Date: Fri, 23 Sep 2011 15:46:13 -0600
Subject: [MS_AccessPros] Calculating In Stock quantities

Hi all,

I am trying to calculate stock levels of parts as they are used to make an
item. I have a form for which I did a query that shows the following.

*Parts_Inventory_Query* Part ID Part Number Part Name Part Description
Unit_Price Total_Units_Purchased: Quantity Used In_Stock: 1 12345 Gorp
Something $1.08 1100 10 1090 1 12345 Gorp Something $1.08 1100 10 1090 1
12345 Gorp Something $1.08 1100 10 1090 1 12345 Gorp Something $1.08 1100
10 1090 1 12345 Gorp Something $1.08 1100 2 1098 1 12345 Gorp Something
$1.08 1100 2 1098 1 12345 Gorp Something $1.08 1100 2 1098 1 12345 Gorp
Something $1.08 1100 2 1098
The In_Stock is based on this formula used in the control source -
=[Total_Units_Purchased]-[Quantity_Used]

Total Units Purchased is taken from the main form.

What I need it to do is to take the total units purchased and then subtract
the quantity used when I enter it into my Inventory_Items form that shows
part usage for each item made.

The above part has been used twice, in 2 different pieces, one 10 parts, and
the other 2 parts. For some reason it is showing the 10 pieces several
times, and the same for the 2 pieces. I is also not subtracting the items
correctly.

I have tried to do this 2 ways, 1. - Put the In_Stock and Total Units
Purchased into a query and then just putting it into the control source for
each field.

My record source is as follows for the query.

SELECT Parts_Inventory.Part_ID, Parts_Inventory.Part_Number,
Parts_Inventory.[Part_ Name], Parts_Inventory.Part_Description,
Parts_Purchase.Pack_Amount, Parts_Purchase.Quantity_Purchased,
Parts_Inventory.Unit_Price, Inventory_Items_BOM.Quantity_Used,
Inventory_Items_BOM.Item_ID, [Pack_Amount]*[Quantity_Purchased] AS
Total_Units_Purchased, ([Total_Units_Purchased])-([Quantity_Used]) AS
In_Stock FROM (Parts_Inventory INNER JOIN Inventory_Items_BOM ON
Parts_Inventory.Part_Number=Inventory_Items_BOM.Part_Number) INNER JOIN
Parts_Purchase ON Parts_Inventory.Part_Number=Parts_Purchase.Part_Number;

Any assistance would be greatly appreciated, I have been working on this for
weeks and have no idea how else to do it.

Thank you,

Deb

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

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

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/