Jumat, 23 September 2011

[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]

__._,_.___
Recent Activity:
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

.

__,_._,___

Tidak ada komentar:

Posting Komentar