Want to place a 0.00 amount for a field that does not have a value

[cous261]

Have a table that I'm extracting from the State mainframe that contains info about our Tranactions for our Agency for the current year. I'm having to break out this data into seperate queries to get our different Divisions allotments, Encumbrances, and our expenditures. The problem I'm having is not being able to refrence the data back together as the allotments may have a transaction objcode but not a matching Expenditure Objcode and can be the other way around. I have a table which I'm using that contain all the objcodes that I pull the description name for the objcode and trying to figure out how to make that table be set as the Standard for the objcode so that it will pull in the Objcode so that if their is not a amount I can have it place $0.00 in the amount field for that Division's Objcode so I can match it to expenditure for that Objcode and vise versa. Example would be:

Objcode ObjName ALLOTMENTS EXPENDITURES Encumbrance
230010 PRINTING 50,000.00 0.00 10,798.11
230030 PHOTOCOPY 0.00 0.00 22.14
230040 BOOK BIND 0.00 1,404.50 0.00
In this example I would only get a allotment amount for the 230010 code and not the other two and I need to get a 0.00 amount for them to match the Expenditure and Encumbrance when I try to create the report above.


This is the query to extract the allotments but I can't figure out how to create a 0.00 amount for a objcode if there is not a alloment for that objcode. Note your see also where I change the Trn_amt to from a neg to pos number due to the mainframes function and need to do this for viewing purpose of the staff.

TranHist2009DataTbl.FID, TranHist2009DataTbl.BE_CD, TranHist2009DataTbl.IBI, TranHist2009DataTbl.CAT, TranHist2009DataTbl.APPR_YR, TranHist2009DataTbl.GL_CD, TranHist2009DataTbl.OBJ, ObjectCodeNameTbl.OBJ_NM, TranHist2009DataTbl.PROGRAM_STD, TranHist2009DataTbl.PROGRAM_AGENCY, TranHist2009DataTbl.PROD, TranHist2009DataTbl.FUND_EXT, TranHist2009DataTbl.GL_EXT, TranHist2009DataTbl.OBJ_EXT, TranHist2009DataTbl.PROGRAM_EXT, TranHist2009DataTbl.INTER_INTRA, TranHist2009DataTbl.TRN_CD, TranHist2009DataTbl.EXOP_ID, TranHist2009DataTbl.EXOP_VER_NBR, TranHist2009DataTbl.TRN_DT, TranHist2009DataTbl.TRN_AMT, [trn_amt]<0 AS TranAmt, IIf([tranamt]<0,[TRN_AMT]*[tranamt],[Trn_Amt]*-1) AS TranActionAmt, TranHist2009DataTbl.AGENCY_DOC_NBR, TranHist2009DataTbl.VENDOR_ID, TranHist2009DataTbl.VENDOR_NM1, TranHist2009DataTbl.NBI, TranHist2009DataTbl.SCND_DOC_NBR, TranHist2009DataTbl.OTHER_SUBVENDOR_ID, TranHist2009DataTbl.INVOICE_NBR, TranHist2009DataTbl.BF_ORG, TranHist2009DataTbl.BF_EXOP, TranHist2009DataTbl.BF_OBJ, TranHist2009DataTbl.BF_CAT, TranHist2009DataTbl.CHK_ID, TranHist2009DataTbl.CHK_DT, TranHist2009DataTbl.TRN_DESC, TranHist2009DataTbl.ORGN_FUND_SOURCE, TranHist2009DataTbl.OTHER_DOC_NBR, TranHist2009DataTbl.ADD_DT, TranHist2009DataTbl.TRN_QTY, TranHist2009DataTbl.PROD_UNIT, TranHist2009DataTbl.PROD_TIME, TranHist2009DataTbl.FINAL_PYMT_IND, TranHist2009DataTbl.SITE, TranHist2009DataTbl.USER_NBR, TranHist2009DataTbl.AVBL_IND, TranHist2009DataTbl.ALI, TranHist2009DataTbl.ADD_TS, TranHist2009DataTbl.TERMINAL, TranHist2009DataTbl.EXOP_SET, TranHist2009DataTbl.VOUCHER_TYPE, TranHist2009DataTbl.CLEAR_FUND, TranHist2009DataTbl.REVOLVE_FUND, TranHist2009DataTbl.EXOP_OVERRIDE_IND, TranHist2009DataTbl.GROUPING, TranHist2009DataTbl.BATCH_ID, TranHist2009DataTbl.AVBL_OVERRIDE_IND, TranHist2009DataTbl.BOOKKEEPING, TranHist2009DataTbl.SUBS_GL, TranHist2009DataTbl.SUBS_EXT_GL, TranHist2009DataTbl.AVBL_OVERRIDE_GP, TranHist2009DataTbl.MEMO_BLKT, TranHist2009DataTbl.PROCESS_IND, TranHist2009DataTbl.BF_APPR_YR, TranHist2009DataTbl.MINORITY, TranHist2009DataTbl.PRIOR_PERIOD, TranHist2009DataTbl.PROP_BEG, TranHist2009DataTbl.RECORD_TYPE, TranHist2009DataTbl.CTRT_GRNT_LOC, TranHist2009DataTbl.CTRT_GRNT_FLOW, TranHist2009DataTbl.PROP_QTY, TranHist2009DataTbl.TRN_USE_IND, TranHist2009DataTbl.CTRT_GRNT_USE_IND, TranHist2009DataTbl.PROJECT_ID, TranHist2009DataTbl.CFI, TranHist2009DataTbl.BF_CFI, TranHist2009DataTbl.SWDN, TranHist2009DataTbl.ACCRUAL, TranHist2009DataTbl.GRANT_ALI, TranHist2009DataTbl.GRANT_AVBL, TranHist2009DataTbl.SOURCE, TranHist2009DataTbl.CAT_EXT, TranHist2009DataTbl.CONTRACT_ALI, TranHist2009DataTbl.CONTRACT_AVBL, TranHist2009DataTbl.PROJECT_ALI, TranHist2009DataTbl.PROJECT_AVBL, TranHist2009DataTbl.ASOF_MTH_YR, TranHist2009DataTbl.FY_END, TranHist2009DataTbl.IW_DT, CatTbl.CAT_NM, OCATbl.OCA_NM
FROM ((TranHist2009DataTbl INNER JOIN CatTbl ON TranHist2009DataTbl.CAT = CatTbl.CAT) INNER JOIN OCATbl ON TranHist2009DataTbl.OCA = OCATbl.OCA) INNER JOIN ObjectCodeNameTbl ON TranHist2009DataTbl.OBJ = ObjectCodeNameTbl.OBJ
GROUP BY TranHist2009DataTbl.OLO, TranHist2009DataTbl.L2_CD, TranHist2009DataTbl.L3_CD, TranHist2009DataTbl.L4_CD, TranHist2009DataTbl.L5_CD, "45" & [L2_cd], "45" & [L2_cd] & [L3_cd] & [L4_cd] & [L5_cd], TranHist2009DataTbl.AU_CD, TranHist2009DataTbl.GRANT_NBR, TranHist2009DataTbl.GRANT_YR, TranHist2009DataTbl.CONTRACT_NBR, TranHist2009DataTbl.CONTRACT_YR, TranHist2009DataTbl.OCA, TranHist2009DataTbl.GF_CD, TranHist2009DataTbl.SF_CD, TranHist2009DataTbl.FID, TranHist2009DataTbl.BE_CD, TranHist2009DataTbl.IBI, TranHist2009DataTbl.CAT, TranHist2009DataTbl.APPR_YR, TranHist2009DataTbl.GL_CD, TranHist2009DataTbl.OBJ, ObjectCodeNameTbl.OBJ_NM, TranHist2009DataTbl.PROGRAM_STD, TranHist2009DataTbl.PROGRAM_AGENCY, TranHist2009DataTbl.PROD, TranHist2009DataTbl.FUND_EXT, TranHist2009DataTbl.GL_EXT, TranHist2009DataTbl.OBJ_EXT, TranHist2009DataTbl.PROGRAM_EXT, TranHist2009DataTbl.INTER_INTRA, TranHist2009DataTbl.TRN_CD, TranHist2009DataTbl.EXOP_ID, TranHist2009DataTbl.EXOP_VER_NBR, TranHist2009DataTbl.TRN_DT, TranHist2009DataTbl.TRN_AMT, TranHist2009DataTbl.AGENCY_DOC_NBR, TranHist2009DataTbl.VENDOR_ID, TranHist2009DataTbl.VENDOR_NM1, TranHist2009DataTbl.NBI, TranHist2009DataTbl.SCND_DOC_NBR, TranHist2009DataTbl.OTHER_SUBVENDOR_ID, TranHist2009DataTbl.INVOICE_NBR, TranHist2009DataTbl.BF_ORG, TranHist2009DataTbl.BF_EXOP, TranHist2009DataTbl.BF_OBJ, TranHist2009DataTbl.BF_CAT, TranHist2009DataTbl.CHK_ID, TranHist2009DataTbl.CHK_DT, TranHist2009DataTbl.TRN_DESC, TranHist2009DataTbl.ORGN_FUND_SOURCE, TranHist2009DataTbl.OTHER_DOC_NBR, TranHist2009DataTbl.ADD_DT, TranHist2009DataTbl.TRN_QTY, TranHist2009DataTbl.PROD_UNIT, TranHist2009DataTbl.PROD_TIME, TranHist2009DataTbl.FINAL_PYMT_IND, TranHist2009DataTbl.SITE, TranHist2009DataTbl.USER_NBR, TranHist2009DataTbl.AVBL_IND, TranHist2009DataTbl.ALI, TranHist2009DataTbl.ADD_TS, TranHist2009DataTbl.TERMINAL, TranHist2009DataTbl.EXOP_SET, TranHist2009DataTbl.VOUCHER_TYPE, TranHist2009DataTbl.CLEAR_FUND, TranHist2009DataTbl.REVOLVE_FUND, TranHist2009DataTbl.EXOP_OVERRIDE_IND, TranHist2009DataTbl.GROUPING, TranHist2009DataTbl.BATCH_ID, TranHist2009DataTbl.AVBL_OVERRIDE_IND, TranHist2009DataTbl.BOOKKEEPING, TranHist2009DataTbl.SUBS_GL, TranHist2009DataTbl.SUBS_EXT_GL, TranHist2009DataTbl.AVBL_OVERRIDE_GP, TranHist2009DataTbl.MEMO_BLKT, TranHist2009DataTbl.PROCESS_IND, TranHist2009DataTbl.BF_APPR_YR, TranHist2009DataTbl.MINORITY, TranHist2009DataTbl.PRIOR_PERIOD, TranHist2009DataTbl.PROP_BEG, TranHist2009DataTbl.RECORD_TYPE, TranHist2009DataTbl.CTRT_GRNT_LOC, TranHist2009DataTbl.CTRT_GRNT_FLOW, TranHist2009DataTbl.PROP_QTY, TranHist2009DataTbl.TRN_USE_IND, TranHist2009DataTbl.CTRT_GRNT_USE_IND, TranHist2009DataTbl.PROJECT_ID, TranHist2009DataTbl.CFI, TranHist2009DataTbl.BF_CFI, TranHist2009DataTbl.SWDN, TranHist2009DataTbl.ACCRUAL, TranHist2009DataTbl.GRANT_ALI, TranHist2009DataTbl.GRANT_AVBL, TranHist2009DataTbl.SOURCE, TranHist2009DataTbl.CAT_EXT, TranHist2009DataTbl.CONTRACT_ALI, TranHist2009DataTbl.CONTRACT_AVBL, TranHist2009DataTbl.PROJECT_ALI, TranHist2009DataTbl.PROJECT_AVBL, TranHist2009DataTbl.ASOF_MTH_YR, TranHist2009DataTbl.FY_END, TranHist2009DataTbl.IW_DT, CatTbl.CAT_NM, OCATbl.OCA_NM
HAVING (((TranHist2009DataTbl.GL_CD) Like "931*") AND ((TranHist2009DataTbl.CFI)<>"c"));

 

[VRoscioli]

You will want to use what is called an "outer join" (Google this). I can't tell exactly which tables you want to use this on, as the labels you used in your description do not match anything from the query.

-V

 

[dhookom]

Also, is there a reason why you have "GROUP BY" in your query when there is no SUM, AVG, COUNT, or other aggregate? The query might perform better if you moved the criteria from the HAVING clause to a WHERE clause.

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

Take a look at the NZ function.


Randy

 

[cous261]

Thanks guys for your replies as this is the busy time of yr for me and I've not got back here till now. Will try and clear up some of the confusion in my next reply and again I really apperciate your replies and help. Maybe the next next reply will be more clear as this process is dealing with over 300,000 transactions and is a new task for me and one like I said I really apperciate the help. I think it will be a simple thing but then again it may not so I'll try and get a reply up on the board this weekend for sure as I'll have a little breathing time. Thanks again for you help and look forward to seeing your next replies. Have a blessed day and Fourth! Remember to say a prayer for our service men and women.

 

[kjv1611]

You can do what you want, but I'd highly suggest using aliases for your table names. Wow that's a lot of fields, or so it looks, b/c of using the table names so many times!

And it'd probably make things easier to format things so that there are definite visual breaks between each SQL clause.

Maybe like this (I had to add the SELECT part, since there was none in what was posted):

SELECT	th.FID, th.BE_CD, th.IBI, th.CAT, th.APPR_YR, th.GL_CD, th.OBJ,
		oc.OBJ_NM, th.PROGRAM_STD, th.PROGRAM_AGENCY, th.PROD, th.FUND_EXT,
		th.GL_EXT, th.OBJ_EXT, th.PROGRAM_EXT, th.INTER_INTRA, th.TRN_CD,
		th.EXOP_ID, th.EXOP_VER_NBR, th.TRN_DT, th.TRN_AMT, [trn_amt]<0 AS TranAmt,
		[HIGHLIGHT]IIf([tranamt]<0,[TRN_AMT]*[tranamt],[Trn_Amt]*-1) AS TranActionAmt,[/HIGHLIGHT]
		th.AGENCY_DOC_NBR, th.VENDOR_ID, th.VENDOR_NM1, th.NBI, th.SCND_DOC_NBR,
		th.OTHER_SUBVENDOR_ID, th.INVOICE_NBR, th.BF_ORG, th.BF_EXOP, th.BF_OBJ,
		th.BF_CAT, th.CHK_ID, th.CHK_DT, th.TRN_DESC, th.ORGN_FUND_SOURCE,
		th.OTHER_DOC_NBR, th.ADD_DT, th.TRN_QTY, th.PROD_UNIT, th.PROD_TIME,
		th.FINAL_PYMT_IND, th.SITE, th.USER_NBR, th.AVBL_IND, th.ALI, th.ADD_TS,
		th.TERMINAL, th.EXOP_SET, th.VOUCHER_TYPE, th.CLEAR_FUND, th.REVOLVE_FUND,
		th.EXOP_OVERRIDE_IND, th.[GROUPING], th.BATCH_ID, th.AVBL_OVERRIDE_IND,
		th.BOOKKEEPING, th.SUBS_GL, th.SUBS_EXT_GL, th.AVBL_OVERRIDE_GP, th.MEMO_BLKT,
		th.PROCESS_IND, th.BF_APPR_YR, th.MINORITY, th.PRIOR_PERIOD, th.PROP_BEG,
		th.RECORD_TYPE, th.CTRT_GRNT_LOC, th.CTRT_GRNT_FLOW, th.PROP_QTY, th.TRN_USE_IND,
		th.CTRT_GRNT_USE_IND, th.PROJECT_ID, th.CFI, th.BF_CFI, th.SWDN, th.ACCRUAL,
		th.GRANT_ALI, th.GRANT_AVBL, th.SOURCE, th.CAT_EXT, th.CONTRACT_ALI,
		th.CONTRACT_AVBL, th.PROJECT_ALI, th.PROJECT_AVBL, th.ASOF_MTH_YR, th.FY_END,
		th.IW_DT, CatTbl.CAT_NM, OCATbl.OCA_NM
FROM	TranHist2009DataTbl th
			INNER JOIN
		CatTbl 
			ON th.CAT = CatTbl.CAT
			INNER JOIN
		OCATbl
			ON th.OCA = OCATbl.OCA
			INNER JOIN
		ObjectCodeNameTbl oc
			ON th.OBJ = oc.OBJ
GROUP BY th.OLO, th.L2_CD, th.L3_CD, th.L4_CD, th.L5_CD, "45" & [L2_cd], "45" & [L2_cd] & [L3_cd] & [L4_cd] & [L5_cd], th.AU_CD, th.GRANT_NBR, th.GRANT_YR, th.CONTRACT_NBR, th.CONTRACT_YR, th.OCA, th.GF_CD, th.SF_CD, th.FID, th.BE_CD, th.IBI, th.CAT, th.APPR_YR, th.GL_CD, th.OBJ, oc.OBJ_NM, th.PROGRAM_STD, th.PROGRAM_AGENCY, th.PROD, th.FUND_EXT, th.GL_EXT, th.OBJ_EXT, th.PROGRAM_EXT, th.INTER_INTRA, th.TRN_CD, th.EXOP_ID, th.EXOP_VER_NBR, th.TRN_DT, th.TRN_AMT, th.AGENCY_DOC_NBR, th.VENDOR_ID, th.VENDOR_NM1, th.NBI, th.SCND_DOC_NBR, th.OTHER_SUBVENDOR_ID, th.INVOICE_NBR, th.BF_ORG, th.BF_EXOP, th.BF_OBJ, th.BF_CAT, th.CHK_ID, th.CHK_DT, th.TRN_DESC, th.ORGN_FUND_SOURCE, th.OTHER_DOC_NBR, th.ADD_DT, th.TRN_QTY, th.PROD_UNIT, th.PROD_TIME, th.FINAL_PYMT_IND, th.SITE, th.USER_NBR, th.AVBL_IND, th.ALI, th.ADD_TS, th.TERMINAL, th.EXOP_SET, th.VOUCHER_TYPE, th.CLEAR_FUND, th.REVOLVE_FUND, th.EXOP_OVERRIDE_IND, th.GROUPING, th.BATCH_ID, th.AVBL_OVERRIDE_IND, th.BOOKKEEPING, th.SUBS_GL, th.SUBS_EXT_GL, th.AVBL_OVERRIDE_GP, th.MEMO_BLKT, th.PROCESS_IND, th.BF_APPR_YR, th.MINORITY, th.PRIOR_PERIOD, th.PROP_BEG, th.RECORD_TYPE, th.CTRT_GRNT_LOC, th.CTRT_GRNT_FLOW, th.PROP_QTY, th.TRN_USE_IND, th.CTRT_GRNT_USE_IND, th.PROJECT_ID, th.CFI, th.BF_CFI, th.SWDN, th.ACCRUAL, th.GRANT_ALI, th.GRANT_AVBL, th.SOURCE, th.CAT_EXT, th.CONTRACT_ALI, th.CONTRACT_AVBL, th.PROJECT_ALI, th.PROJECT_AVBL, th.ASOF_MTH_YR, th.FY_END, th.IW_DT, CatTbl.CAT_NM, OCATbl.OCA_NM
HAVING (((th.GL_CD) Like "931*") AND ((th.CFI)<>"c"));

Is the [HIGHLIGHT]highlighted[/HIGHLIGHT] portion of the code the reason you're using the Grouping function? I know I didn't see it until I broke it all out... and I didn't mess with the Group By and Having clauses yet, figured I'd leave that for now in case you ended up getting around using those..

--

"If to err is human, then I must be some kind of human!" -Me