RE: [MS_AccessPros] Calculating In Stock quantities

I think you must create totals queries and then join these to the parts table. Just start with the table and change it to a totals query. Group by the Part_Number and count or sum whatever fields you need.

If you can't figure this out, come back with the significant table and field names and what exactly you want to display in the final query.

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: beader59@gmail.com
Date: Sat, 24 Sep 2011 09:05:14 -0600
Subject: 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]

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