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
Proff52

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
DavidE

Multi-Boot W7_Pro_x64 W8.1_Pro_x64 W10_Pro_x64
 
 

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
My System SpecsSystem Spec
01 Aug 2014   #3
Proff52

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
Mustafa
My System SpecsSystem Spec
.

Reply

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




Thread Tools Search this Thread
Search this Thread:

Advanced Search




Similar help and support threads
Thread Forum
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
Customised wallpaper request, not sure where to put this
Sorry if this was in the wrong place; i considered putting it in the custom wallpapers thread but this isnt my own wallpaper and im not sure if im allowed to request in there. I was wondering if someone would be kind enough to help me with the wallpaper attached, id like the windows logos to both...
Customization
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...
Software


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 15:06.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App