in a quandary over queries

[Valerie]

I am almost finished building a database in which to
transfer my company's inventory. However, I'm having
trouble with one particular query. Here's the scenario:

I have input all my parts into a parts table. I have a
related table for my initial inventory (physical count),
another for my purchase orders, and another for my bills
of material. Now I am trying to use queries to establish
my current inventory by calculating my initial inventory
plus any purchase orders less any items used in bills of
material. My calculations all work fine. The problem is,
the only way a product will show up in my current
inventory query is if it has A) a quantity listed in the
initial inventory table, AND B) been ordered on a purchase
order since we took inventory AND C) been used on at least
one bill of material since we took inventory. I don't
necessarily want parts with a zero quantity to show up on
the current inventory (but if they did, that would be
fine), but I certainly want all items for which we have
quantities on hand to show up.

It appears to have something to do with my relationships
because when I fool with them, I get different results.
Sometimes an item will appear 4,5 even 15 times with
quantities that are relevant to a totally different
product; other times it won't show up at all. When I
deleted the one product I had in my "trial run" that met
all 3 criteria, all items disappeared from my current
inventory.

PLEASE HELP!

 

[Allen Browne]

Valerie, this sounds like an issue with the JOIN type in your queries.

Presumably you have a query that includes the Parts table as well as the
PurchaseOrder table (and others). In query design view, double-click the
line joining the two tables. Access pops up a dialog offering 3 choices. The
default is:
Only records that match both tables
and that is why you do not get a record if it does not appear in both
tables. Choose the option:
All Parts, and any matches from PurchaseOrder

That changes the JOIN in the FROM clause of the SQL statement to an outer
join. Because you need to do this with several tables, it is possible to run
into a problem with "ambiguous outer join" messages. If you hit this, you
may need to continue making outer joins on other related tables as well,
with all the arrows pointing *away* from the Parts table (assuming that it
is the heart of what you want to know about).

More info on outer joins:
http://allenbrowne.com/casu-02.html

General suggestions about inventory quantites:
http://allenbrowne.com/AppInventory.html

 

[Valerie]

Thanks so much. I came across this info in searching
through my books (after hours of doing so before posting
my question) while awaiting an answer. And just as you
said, I encountered the "ambiguous outer join" error
message. Will continue on as you suggested in the hopes
that I can FINALLY get this right. Thanks again for the
reply and for the links. I'll check them out post haste!

Valerie