Data Not Showing Up If Empty in One Table

[Pluong91]

Hello Accessforums.net!

I'm an accountant for a alcohol distributing company and have a very strong background in Excel, I however have no knowledge of Access and looking to expend that knowledge.

The project I'm working on is trying to consolidate multiple reports together to track the life cycle of all of our inventory items.



The lifecycle is as follows:
Beg Inventory-(plus)Sales-(less)SL&B-Donations-(less)Transfers-Ending Inventory

The problem I'm running into is if there is no activity in one of the reports referenced in the lifecycle, then when I run the query that particular item will not show up.
My initial query is showing 381 results while ideally it should be in the 800+ based on the pivot tables I'm running in excel.

How do I get all the "item name ID" to be displayed even if there is no activity for that item? Maybe display it as blank or a 0.

Thank you forum members!

I tried to upload the report but unfortunately it was too large.

[ranman256]

if you have a query with inner joined tables, you will get zero results if 1 table is empty.
You will want an OUTER join.
this will show all items in the main table and those in missing or otherwise in the sub table.

[Pluong91]

I found a script for Outer Join, would I just enter it into the SQL area?

Also would this work on every table? Ideally I would want it to find the unique item numbers in each table.
For instance something might not have beg inventory, but might have receiving and/if those items are all sold, then it would not have ending inventory either.

[Micron]

Might help if you posted a pic of your relationships window, assuming you've created them. See "how to attach files" in menu bar at the top of the forum window.

[Gicu]

You could create a union query on your 5 tables to get you the comprehensive list of all unique item numbers in your db (here is a link, I have not looked at it but the title matches the intent: https://www.youtube.com/watch?v=Y1B2W6VfWaw).

Then you use that query in another one where you have outer joins from it to your 5 tables, bring the item number from the union query and the related info from each of the appropriate tables.

Cheers,