Query based from multiple inputs

[AC5FF]

I have an inventory database that I would like to automate a little better but my attempts have not been working as expected.
To start; I have a table with a list of parts that are consumed from inventory any time I build a widget. The table lists the part number, location, and quantity needed for each widget. I would like to run a query that will give me a table with all the data I need to subtract parts from my inventory each time I build a batch of widgets.

I initially thought that I could create a query based off of the parts table using a number of conditions that are entered when the query runs. I.E. What quantity was built, the date, and finally the configuration (can be 1 of 7 versions of the widget). Then based on these entries build my table accordingly.

But that didn't work. When I run the query like this it keeps cycling through asking me the conditional questions; presumably for each part number in the build table. So, instead I thought I'd write a sub-query that would ask those conditional questions, and then link that sub-query to the table. This does not work because I do not have a table associated with the query.

Am I looking at this wrong? Maybe a better way to go about doing this?

Thanks!

 

[PNGBill]

Each Widget Version should have a defined list of parts and related qtys.
This should be in a table - say tblWidgetComponents
The first field would be fldWidgetID
2nd field fldWidgetName
The other fileds would be the components of that specific Widget.

Within your database, when ever one Widget xyz is produced, a query will refer to this table and alter the stock on hand of each of the related cpart numbers from the tblInventory accordingly.

No additional table should be required to alter the stock on hand of widget components.

You would need to decide when to efect the transaction.
This could be as the Widget was produced or as each batch of Widgets was completed.
This really depends on what procedures your business has.