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

Posted on 2007-10-19
Medium Priority
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 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 20110264
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

ID: 20110314
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;

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

864 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