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]
Jumat, 23 September 2011
[MS_AccessPros] Calculating In Stock quantities
__._,_.___
MARKETPLACE
.
__,_._,___
Langganan:
Posting Komentar (Atom)
Tidak ada komentar:
Posting Komentar