Sabtu, 24 September 2011

Re: [MS_AccessPros] Calculating In Stock quantities

 

Hi Duane,

Thank you for your reply! They are group by Part_Number, my apologies for
not including that. Inventory Items BOM is connected to Parts_Inventory by
Part Number and Inventory_Items is connected to Inventory_Items_BOM by
Item_ID. Parts_Purchase is connected to Parts_Inventory by Part_Number.
If a "Part" has only one entry (purchase) showing in the Part_Inventory
form, then it will do the calculation just fine, but if, as shown with my
example, it has several purchases listed, the it is unable to calculate
correctly. Originally I tried calculating it through the query, but when
that didn't work, I tried doing the calculations in the control source
fields. Both ways work as long as their is only one purchase shown in the
form for the part.

Could you explain a bit more on how to create the totals queries of
Inventory_Items_BOM and Parts_Purchase? This is my first attempt at doing
calculations in a query and I'm not sure I am doing it correctly.

There is a copy of my DB in the assistance needed folder if you would like
to take a look...

Thank you for all of your help!

Deb

On Fri, Sep 23, 2011 at 9:00 PM, Duane Hookom <duanehookom@hotmail.com>wrote:

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

[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