Inventory Qty on Hand

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database with many items with shipments and returns. Is there a way
to do a query as you would a report showing quantity on hand for each item?
I would like to use this qty combined with the items table to show qty on
hand. The entries to the items are made in a datasheet with a subform not a
single form so I cannot have a footer formula for each item.
 
Hi,


If you have, basically, two fields, ItemID and Qty, where Qty is + for what
get added to inventory and - for what get out of inventory, then a TOTAL
query will do the job.


Open a new query, bring the table, click on the Summation button on the
toolbar (the one with the Capital Sigma, a M rotated by 90 degree) and a new
line, total, appears in the grid. Bring the field ItemID in the grid, keep
the proposed GroupBy. Bring the field Qty in the grid, change the GroupBy to
SUM.

You are done.

Use that query where you need the quantity in inventory (read only). You can
create a form or a report based on a query (not just based on a table).




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top