• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

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

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?
1 Solution
Patrick MatthewsCommented:
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.
simplyssAuthor Commented:
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;
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now