Filters in Queries

Question

I have 4 total queries and 3 separate queries that get budget information from a budget table and a account number table, which go into the 4 query. For the query named bud11 I have some accounts that are not in the other 2 queries (bud10 and actvproj). When I run bud11 by itself, all the accounts I need, show up. But when I run the 4th query (CCQUERY)which has Bud11, bud10, and actvproj data, there are some accounts missing.....below are the 4 sql views for each query.

BUD11QUERY

SELECT BUDGET.FISCAL_YEAR, BUDGET.BUDGET_NBR, BUDGET.ACCT_UNIT, BUDGET.ACCOUNT, ZBASED.ACCOUNT_DESC, BUDGET.DB_AMOUNT_01+BUDGET.DB_AMOUNT_02+BUDGET.DB_AMOUNT_03+BUDGET.DB_AMOUNT_04+BUDGET.DB_AMOUNT_05+BUDGET.DB_AMOUNT_06+BUDGET.DB_AMOUNT_07+BUDGET.DB_AMOUNT_08+BUDGET.DB_AMOUNT_09+BUDGET.DB_AMOUNT_10+BUDGET.DB_AMOUNT_11+BUDGET.DB_AMOUNT_12+BUDGET.CR_AMOUNT_01+BUDGET.CR_AMOUNT_02+BUDGET.CR_AMOUNT_03+BUDGET.CR_AMOUNT_04+BUDGET.CR_AMOUNT_05+BUDGET.CR_AMOUNT_06+BUDGET.CR_AMOUNT_07+BUDGET.CR_AMOUNT_08+BUDGET.CR_AMOUNT_09+BUDGET.CR_AMOUNT_10+BUDGET.CR_AMOUNT_11+BUDGET.CR_AMOUNT_12 AS Bud11
FROM BUDGET INNER JOIN ZBASED ON BUDGET.ACCOUNT = ZBASED.ACCOUNT
WHERE (((BUDGET.FISCAL_YEAR)=2011) AND ((BUDGET.BUDGET_NBR)=2) AND ((ZBASED.ACCT_UNIT)=[BUDGET].[ACCT_UNIT] And (ZBASED.ACCT_UNIT)=[Cost Center]))
ORDER BY ZBASED.ACCOUNT; 

BUD10QUERY

SELECT BUDGET.FISCAL_YEAR, BUDGET.BUDGET_NBR, BUDGET.ACCT_UNIT, BUDGET.ACCOUNT, ZBASED.ACCOUNT_DESC, BUDGET.DB_AMOUNT_01+BUDGET.DB_AMOUNT_02+BUDGET.DB_AMOUNT_03+BUDGET.DB_AMOUNT_04+BUDGET.DB_AMOUNT_05+BUDGET.DB_AMOUNT_06+BUDGET.DB_AMOUNT_07+BUDGET.DB_AMOUNT_08+BUDGET.DB_AMOUNT_09+BUDGET.DB_AMOUNT_10+BUDGET.DB_AMOUNT_11+BUDGET.DB_AMOUNT_12+BUDGET.CR_AMOUNT_01+BUDGET.CR_AMOUNT_02+BUDGET.CR_AMOUNT_03+BUDGET.CR_AMOUNT_04+BUDGET.CR_AMOUNT_05+BUDGET.CR_AMOUNT_06+BUDGET.CR_AMOUNT_07+BUDGET.CR_AMOUNT_08+BUDGET.CR_AMOUNT_09+BUDGET.CR_AMOUNT_10+BUDGET.CR_AMOUNT_11+BUDGET.CR_AMOUNT_12 AS Bud10
FROM BUDGET INNER JOIN ZBASED ON BUDGET.ACCOUNT=ZBASED.ACCOUNT
WHERE (((BUDGET.FISCAL_YEAR)=2010) AND ((BUDGET.BUDGET_NBR)=1) AND ((ZBASED.ACCT_UNIT)=BUDGET.ACCT_UNIT) And ((ZBASED.ACCT_UNIT)=[Cost Center]))
ORDER BY ZBASED.ACCOUNT;

ACTvPROJ QUERY

SELECT BUDGET.FISCAL_YEAR, BUDGET.BUDGET_NBR, BUDGET.ACCT_UNIT, BUDGET.ACCOUNT, ZBASED.ACCOUNT_DESC, BUDGET.DB_AMOUNT_01+BUDGET.DB_AMOUNT_02+BUDGET.DB_AMOUNT_03+BUDGET.DB_AMOUNT_04+BUDGET.DB_AMOUNT_05+BUDGET.DB_AMOUNT_06+BUDGET.DB_AMOUNT_07+BUDGET.DB_AMOUNT_08+BUDGET.DB_AMOUNT_09+BUDGET.DB_AMOUNT_10+BUDGET.DB_AMOUNT_11+BUDGET.DB_AMOUNT_12+BUDGET.CR_AMOUNT_01+BUDGET.CR_AMOUNT_02+BUDGET.CR_AMOUNT_03+BUDGET.CR_AMOUNT_04+BUDGET.CR_AMOUNT_05+BUDGET.CR_AMOUNT_06+BUDGET.CR_AMOUNT_07+BUDGET.CR_AMOUNT_08+BUDGET.CR_AMOUNT_09+BUDGET.CR_AMOUNT_10+BUDGET.CR_AMOUNT_11+BUDGET.CR_AMOUNT_12 AS ActvProj10
FROM BUDGET INNER JOIN ZBASED ON BUDGET.ACCOUNT=ZBASED.ACCOUNT
WHERE (((BUDGET.FISCAL_YEAR)=2010) And ((BUDGET.BUDGET_NBR)=6) And ((ZBASED.ACCT_UNIT)=BUDGET.ACCT_UNIT And (ZBASED.ACCT_UNIT)=[Cost Center]))
ORDER BY ZBASED.ACCOUNT;

CCQUERY

SELECT ZBASED.ACCT_UNIT, CCtable.CenterName, ZBASED.ACCOUNT, ZBASED.ACCOUNT_DESC, qryBUD11.Bud11, qryBUD10.Bud10, qryACTvPROJ.ActvProj10
FROM CCtable, ((ZBASED INNER JOIN qryACTvPROJ ON ZBASED.ACCOUNT = qryACTvPROJ.ACCOUNT) INNER JOIN qryBUD11 ON ZBASED.ACCOUNT = qryBUD11.ACCOUNT) INNER JOIN qryBUD10 ON ZBASED.ACCOUNT = qryBUD10.ACCOUNT
WHERE (((ZBASED.ACCT_UNIT)=[Cost Center]) AND ((CCtable.CenterNo)=[Cost Center]))
ORDER BY ZBASED.ACCOUNT;

Can anyone help me figure out why with CCQUERY some of the accounts are missing??

Answer 1

Try LEFT JOINS rather than INNER JOINS in the final query.