Windows 7 Forums
Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.

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

31 Jul 2014   #1

Windows 7 Professional 64 bit
How to make a customised query to link same fields in different tables

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?

My System SpecsSystem Spec
31 Jul 2014   #2

Multi-Boot W7_Pro_x64 W8.1_Pro_x64 W10_Pro_x64 +Linux_VMs +Chromium_VM

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 ?

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

My System SpecsSystem Spec
01 Aug 2014   #3

Windows 7 Professional 64 bit
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
My System SpecsSystem Spec


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

Thread Tools

Similar help and support threads
Thread Forum
Explorer: can I make a link/shortcut to a folder in another location?
I like to organize my files and folders very thoroughly. Sometimes I have a folder full of files that arguably belong in multiple locations. I'm wondering if there's a way to create a sort of "wormhole" that leads to another location. Let me give you an example: Let's say I went on vacation and...
General Discussion
Make the tab key only select text fields, not menus and other stuff
Hello, When I've got any kind of dialogue box open with text fields, I use the tab key to hop between the text fields. But, doing this also hops to any pop-up menus, which means I can change the thing in the pop-up menu using the up/down cursor keys, which might possibly be useful in a very few...
General Discussion
How to make explorer see/process a hidden desktop link at startup
I'm sure I can safely assume nobody has ever asked this question before (or probably even thought of why one might want such a thing!). You see, I have this program that I made which I use to send running apps to the system tray. I use the program exclusively through explorer's desktop link...
General Discussion
How to make link's short on your sig
I want to put a few tut's on my sig and I just put system restore and it's the full URL. Just wondering how to put it as just system restore as i seen in others
Chillout Room
How to combine fields in Access 2010 based on 2 equal fields?
Hi all, Iím really in need of some help from some Access experts! Iím a newbie to Access, but I got myself busy with quite a big project... Iím trying to map out the product supply to shops in the whole country. Iíve come quite far for a starter, but I keep having 3 questions, it would be great...

Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

© Designer Media Ltd

All times are GMT -5. The time now is 02:49.
Twitter Facebook Google+