How to make a customised query to link same fields in different tables

[Proff52]

Hi!I am trying to make a simple inventory program in Access 2007.I have tables for purchases,sales and a master table of the various items names.I have made a look up for item names in the purchases and sales tables from the master table of items so that there is no issue of spelling mistakes and to make it easier to enter item names.I am trying to make a stock sheet record using a query to show me the balances of the various items at a particular date(current date)The master table has fields for opening stock,reorder level and of course item names.In the query design view I have put current date as a formula,linked opening stock,reorder level and item names to the master table.Then I put in a formula to calculate the current balance of each item.The formula is thus:Take the opening stock from the master table add the quantity purchased from the purchases table and deduct the quantity sold from the sales table for each item.But when I run the query it shows me only the items and their current balance quantities for those items which appear in all three tables(ie.master table,purchases and sales table).Any item appearing in any one of the tables but not in the other two tables is not shown at all.So this means that non or slow moving items don`t appear when the query is run.Neither do items that have been purchased and not sold or vice versa.Please how do I solve this problem so that I show correct stock balances for all items at any time?

 

[DavidE]

You need to use an OUTER JOIN to get all the data.

Here is an MS article about MS Access Outer Join:
Creating an outer join query in Access - Access

It seems Access doesn't support a FULL OUTER JOIN, and I think that is what you want.
This article explains a workaround for this:
Implementing the Equivalent of a FULL OUTER JOIN in Microsoft Access ? DatabaseJournal.com

You can also Google for ms access outer join code if you want to find more articles.

hth,
David

 

[Proff52]

Thank You Very Much

Dear David,

Thank you very much for your prompt and helpful reply.I will look up outer join and how to implement it in my database.
Have a wonderful Friday and brilliant weekend
Yours Sincerely
Mustafa