Transaction-Based Inventory Woes: Only Summing the Right Things

[BDibz]

I'm building a little app that calculates the inventory gap between whats on-hand and what is forecast to be needed.



This database keeps track of the stock of [Electrodes], which [Part] they are needed to produce, and how many parts can be produced per electrode, [Parts Per Electrode]

For each [Part] there is a [Part Qty] that is the amount of that part that is ordered for the month.

The relationship between parts and electrodes is many-to-many, maintained in a "helper" table.

Here's my issue. I almost have everything working. As you can see below, I can spit out a result of how many electrodes need to be ordered for the month, but the parts are repeated, which means that it is subtracting the electrodes needed from the total stock of electrodes on hand for each entry, and not aggregating the total amount needed to subtract first. That was a confusing sentence - basically, each row assumes that the stock is fully loaded before checking to see if there is "enough" - whereas, in reality, some of the stock should already be accounted for by previous rows.



So, I tried to remove the "Parts" column, so that it is grouped on Electrodes. This is one step forward and two steps back, because now it double counts the inventory (it thinks I have 'n' times the amount of electrodes than I actually do, where 'n' is the number of times that electrode appears in the order).



I hope this explanation made sense. Any ideas how I can get this to work right?

[BDibz]

I solved this by separating into two queries that first find the need by part, and then aggregate the need by electrode.

One question on this method though; if I run a report on the second query in the series, will it run the queries it relies on in series before returning the results?

IE if query [B] depends on query [A], and [A] has a criterion determined by a parameter input on a form... and then from that form I click a button that runs query [B], will query [A] be run first with that criteria applied?

[ssanfu]

Yes.

I have a report that depends on 3 queries.
The first query filters the results based on the criteria (many tables)
The second query (a totals query) is based on the first query (data for several reports)
The third query (summary) is based on the second query. This query is used to limit the fields for the report.

Open the report and get the data results. Tada!!