Need 5 queries with subqueries or can it be done all in one?

[cyberpyxi via AccessMonster.com]

I am having an Update Query issue. I have a Calls form that holds 5 chemicals
and the quantities used of each. I need these quantities to be subtracted
from the Inventory table as I enter calls.
I made a simple Update Query but am receiving undesirable results.
Because there are the 5 spots to enter chemicals, I made 5 update queries.
They preform properly in that the most recent quantity is (ultimately) the
final number put into the Inventory table, however, that is only per column.
If AA5015 was used in the most recent record of Calls, and the ending
quantity on hand is 15, that is the number put into Inventory table for that
item, unless AA5015 was used in a previous record but in Chem2, Chem3, Chem4,
or Chem5.

Any suggestions on how I can make this work? There will never be a time where
the same chemical will be used twice on one record, if that helps.

The only thing I can think of is to do a MAXCallsID query that will take the
highest AutoNumber (Primary Key) from the Calls and use that as the updating
factor and then to put that in the Update query, but I am unsure how to
properly do so. I keep getting errors with it. The MAXCallsID will pull up
the Chem1 ID and QOH for it properly, but I can't seem to integrate it into
an Update.

Ideas?

 

[KARL DEWEY]

Why don't I rain (drizzle) on your parade and suggest a different approach.
Keep a separate record of all transaction for historical purposes.
Use a single table for all transactions - In, Out, Physical Inventory, etc.
Have a field to designate transaction type. If an Out, then in summing the
quanity it would be a negative.
In any Balance query use the last Physical Inventory as a starting point and
sum only that record plus any afterwards.

 

[Ken Sheridan]

Your problem stems from a design flaw. Instead of five columns for the
chemicals you should have a separate table in which each chemical is
represented by a value in a single column, with another column being a
foreign key referencing the primary key of your Calls table, e.g CallID
Another column will record the quantity and if each of the five chemicals
represents some different attribute of each call then you can also have a
column for this in the new table. The primary key of this table would be a
composite one made up of the CallID and Chemical columns.

The quantity on hand of each chemical is then simply the sum of the
quantities per chemical in the new table subtracted from the sum of the
quantities per chemical added to the inventory, for which you'd have another
table. You can do this with a query along these lines:

SELECT Chemical, SUM(Quantity)
- (SELECT NZ(SUM(Quantity),0)
FROM ChemicalsUsed
WHERE ChemicalsUsed.Chemical = StockReceived.Chemical)
As QuantityOnhand
FROM StockReceived
GROUP BY Chemical;

By computing the current stock on hand like this from the base data rather
than updating a value in a table you are guaranteed that the quantity on hand
values will always consistently reflect the stock received and used. If, on
the other hand, you store the current stock position in a table then there is
the possibility of inconsistencies arising.

You can populate the new ChemicalsUsed table easily with a series of five
append queries, each of which inserts the values from the Calls tables
primary key column and from one of the five chemicals columns and quantities
columns into the new table.

For data input the usual arrangement would be form based on the Calls table,
and within it a subform based on the ChemicalsUsed table, the subform being
in continuous form or datasheet view, and having a combo box to select the
chemical, a text box to enter the quantity and any other controls bound to
other columns in the table.

Ken Sheridan
Stafford, England

 

[cyberpyxi via AccessMonster.com]

Done. Based on your suggestion and the one just after, I have done some
redesign to the entire project.
In my calls form, I have a sub-form for my chemicals used. I don't know why I
didn't think of that before. It works like a charm!
I'm still having an issue with an update query on another table based on the
calls form, but I'm sure I can figure that one out in time.
I appreciate your assistance!

Why don't I rain (drizzle) on your parade and suggest a different approach.
Keep a separate record of all transaction for historical purposes.
Use a single table for all transactions - In, Out, Physical Inventory, etc.
Have a field to designate transaction type. If an Out, then in summing the
quanity it would be a negative.
In any Balance query use the last Physical Inventory as a starting point and
sum only that record plus any afterwards.

I am having an Update Query issue. I have a Calls form that holds 5 chemicals
and the quantities used of each. I need these quantities to be subtracted

[quoted text clipped - 19 lines]

Ideas?