Query issue when key does not have both types of information in the table

Posted on 2007-10-19
Last Modified: 2013-11-29
I am using Access and need to add budget information together from 2 different tables in a query. Problem is the budget types are different and the stores that have the budget types sometimes have one budget type and not the other. When I combine the 2, only the stores that have both budget types show up in the query. If one store has a SAU Budget, but not a RES Budget, that store does not appear in the query. How do I fix this?
Question by:simplyss
    LVL 92

    Accepted Solution

    simplyss said:
    >>How do I fix this?

    By changing your INNER JOINs to LEFT JOINs or RIGHT JOINs as needed.

    Please post the SQL you have so far.

    Author Comment

    SELECT tblPropertyAddresses.StoreNumber, tblCapExReserveBalance.ReserveBalance, qrySauPropertyAmounts.SumOfBudAmount, Nz([ReserveBalance])+Nz([SumOfBudAmount]) AS Expr1
    FROM (tblPropertyAddresses INNER JOIN tblCapExReserveBalance ON tblPropertyAddresses.StoreNumber = tblCapExReserveBalance.PropertyNumber) INNER JOIN qrySauPropertyAmounts ON tblPropertyAddresses.StoreNumber = qrySauPropertyAmounts.StoreNumber;

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now