Stock Control - Query

  • Thread starter Thread starter David
  • Start date Start date
D

David

I am working on a Stock Control Database. The data is
held in a number of tables one of which has fields to
cover the Opening qty, Additions, Subtractions and
Adjustments. This table is accessed through a subform
from one of the main forms. Each item is displayed (page
at a time) with the subform showing all the transactions
for that item. I would like to show the up-to-date stock
quantity i.e. the totals from the subform where the stock
quantity = Opening Qty + Additions - Subtractions for
that item only.
Problem 1: I can't get to sum all transactions for that
individual item only.
Problem 2: Assuming I get that sorted out - where and how
do I do the calculations on these totals to get a final
balance i.e. opening stock + additions - subtractions for
that item.
I except that this might be an involved question but
pointers would be appreciated
 
David,

what you need to do is query for that item to get a query
that has only that item. then in the expression builder
create an expression that does the math. You can choose
to show this item, then show that item on your form, or
have a button on the form that will go to a report that
shows that info, the report would be built from the query.

1. Query to get stock item on one query. This is done by
setting the Criteria, you can prompt for user input if
necessary.

2. I am guessing that you have addition and subtraction in
differnt fields, or some way of determining if add or
subtract. Set your query to group in those two groups and
sum them.

3. In criteria create the expression that adds the groups
by the appropriate function to the begining quantity.

Hope this gets you going in the general direction.

Kevin
 
Back
Top