Getting all of the information to appear

[Eric]

I am new to access and I am trying to develop a database
that keeps track of inventory. When a shipment comes in,
a manager types the addition of the items to inventory on
one form. When Inventory goes out, a second form
(recorded to a different table) is filled out. I can get
my query to subtract the reductions from the additions
easily to give me current iventory. But if a particular
part number has no subtractions on record, it does not
appear on the query because there is no corresponding
value for the part number on the "reductions" table.
Similarly, if we have a part number on record but there
are neither additions nor subtractions (i.e. we haven't
bought any of them yet), I can't get the inventory to say
zero. How complicated would it be to get the query to do
this?

 

[Tom Ellison]

Dear Eric:

There are several query design concepts you may need to absorb to put these
pieces together. I'll try to describe them in narrative form, as your post
is narrative and very short on details.

If I were building this, I would ask first just which inventory items should
appear. Do you want to see only those that have been added, whether
subtracted or not? Do you want to see every inventory item even if there is
no activity for it? Or do you want to see only those with either an
addition or a subtraction? These approaches are quite different, so answer
carefully.

You will need to understand JOINs. In the default case you get an INNER
JOIN, which for this situation means you would see only those items that
have both an addition and a subtraction (assuming you are starting with the
table of all inventory items.) If you right click on the line between the
tables in the Query Design Grid you can see there are other options.
Probably if you read this carefully and think about it you will begin to see
what these options mean.

Probably, what you want to do is to show all rows from inventory and only
those rows from additions (or subtractions in the case of the other join)
where some key fields match. This is a rough paraphrase of what it will say
in one of the JOIN options displayed.

Look at this a bit and please let me know how your are progressing.