Account Statement Query

[jasn_78]

Hey guys I have the following problem hope some1 can help

I am trying to write an account statement. I am ok with everything except one thing, I need a field called “OUTSTANDING BALANCE” which is a value of the balance remaning from a previous amount “Balance BForward” (I am fine on this part) but it then removes and “Cash Receipts” and –ve “Journal” entries in the table.

My problem is I can’t figure out how to write an sql statement that calculates the “Balance BForward” minus these other 2 amounts.

This is the table of which it all comes out of
ACOP_ACCT_NO
ACOP_TRX_DATE
ACOP_SEQ
ACOP_TRX_TYPE
ACOP_TRX_AMT
ACOP_GST_AMT
ACOP_TENDER
ACOP_REF
ACOP_NARR
ACOP_SYS_DATE
ACOP_ORIGIN
ACOP_SYSC_NUMBER
ACOP_AREA_NUMBER
ACOP_TILL_NUMBER
ACOP_POINTS
ACOP_RECEIPT_TYPE

So what i have is I did a sum of all transactions up to the ACOP_TRX_DATE for the start of the statement which works fine. But then I need to say add any records in ACOP_TRX_TYPE =“Cash Receipts” or (“Journal” AND a –ve ACOP_TRX_AMT) between 2 dates.

The query I have so far is as follows

SELECT ACMFTBL.ACMF_NUMBER AS ACC_NUMBER, 
	ACMFTBL.ACMF_NAME AS ACC_NAME, 
	ACMFTBL.ACMF_ADDRESS_1 AS ADDRESS1, 
	ACMFTBL.ACMF_ADDRESS_2 AS ADDRESS2, 
	ACMFTBL.ACMF_ADDRESS_3 AS ADDRESS3, 
	ACMFTBL.ACMF_POST_CODE AS POSTCODE, 
	ACMFTBL.ACMF_PHONE_NO AS PHONE_NO, 
	ACMFTBL.ACMF_FAX_NO AS FAX_NO, 
	Round(ACMFTBL.ACMF_CURR_BALANCE,2) AS BALANCE, 
	SYSCTBL.SYSC_COMPANY AS STORE, 
	SYSCTBL.SYSC_ADDR_1 AS STORE_ADDRESS1, 
	SYSCTBL.SYSC_ADDR_2 AS STORE_ADDRESS2, 
	SYSCTBL.SYSC_ADDR_3 AS STORE_ADDRESS3, 
	SYSCTBL.SYSC_POST_CODE AS STORE_POSTCODE, 
	SYSCTBL.SYSC_ABN_NO AS STORE_ABN, 
	ACOPTBL.ACOP_TRX_DATE AS TRX_DATE, 
	Sum(([ACOPTBL]![ACOP_TRX_AMT])*IIf([TRXTYPE]="Cash Receipt",-1,1)) AS TRX_AMOUNT, 
	ACOPTBL.ACOP_TRX_TYPE AS TRXTYPE, 
	ACOPTBL.ACOP_REF AS REFERENCE, 
	ACOPTBL.ACOP_GST_AMT AS GST, 
	IIf(Nz([ACMF_TERMS],0)=0,[SYSCTBL]![SYSC_DEFAULT_TERMS],[ACMFTBL]![ACMF_TERMS]) AS TERMS, ACMFTBL.ACMF_EMAIL, 
	qryACCBFORWARD.BForward, 
	[qryACCBFORWARD]![BForward]+IIf([ACOPTBL]![ACOP_TRX_TYPE]="Cash Receipt",Sum([ACOPTBL]![ACOP_TRX_AMT]*-1),0) AS OUTSTANDING
FROM SYSCTBL, 
	ACOPTBL, 
	qryACCBFORWARD INNER JOIN ACMFTBL ON qryACCBFORWARD.ACOP_ACCT_NO = ACMFTBL.ACMF_NUMBER
WHERE (((ACMFTBL.ACMF_SYSC_NUMBER)=[SYSCTBL].[SYSC_NUMBER]) 
AND ((ACMFTBL.ACMF_NUMBER)=[ACOPTBL].[ACOP_ACCT_NO]) 
AND ((ACOPTBL.ACOP_TRX_DATE) Between [Forms]![frmACCSTATEMENTS]![txtDATEFROM] And [Forms]![frmACCSTATEMENTS]![txtDATETO]) 
AND ((SYSCTBL.SYSC_NUMBER)=[Forms]![frmACCSTATEMENTS]![comboSTORE]))
GROUP BY ACMFTBL.ACMF_NUMBER, 
	ACMFTBL.ACMF_NAME, 
	ACMFTBL.ACMF_ADDRESS_1, 
	ACMFTBL.ACMF_ADDRESS_2, 
	ACMFTBL.ACMF_ADDRESS_3, 
	ACMFTBL.ACMF_POST_CODE, 
	ACMFTBL.ACMF_PHONE_NO, 
	ACMFTBL.ACMF_FAX_NO, 
	SYSCTBL.SYSC_COMPANY, 
	SYSCTBL.SYSC_ADDR_1, 
	SYSCTBL.SYSC_ADDR_2, 
	SYSCTBL.SYSC_ADDR_3, 
	SYSCTBL.SYSC_POST_CODE, 
	SYSCTBL.SYSC_ABN_NO, 
	ACOPTBL.ACOP_TRX_DATE, 
	ACOPTBL.ACOP_TRX_TYPE, 
	ACOPTBL.ACOP_REF, 
	ACOPTBL.ACOP_GST_AMT, 
	IIf(Nz([ACMF_TERMS],0)=0,[SYSCTBL]![SYSC_DEFAULT_TERMS],[ACMFTBL]![ACMF_TERMS]), 
	ACMFTBL.ACMF_EMAIL, 
	qryACCBFORWARD.BForward, 
	ACMFTBL.ACMF_CURR_BALANCE;

Any ideas?

 

[gemma-the-husky]

depends how you hold your transactions/balances, but what you are trying to do sounds unusual

the o/s balance ought to be merely the total of unmatched transactions from a transactions file without reference to the master record. most systems store all transactions types in a single file (eg invoices, cash, journals etc). If you store them in different files, its a bit more work.

it would be unusual to need to construct the balance partly from the master record, and partly from transactions.

what a lot of accounting systems actually do (and it breaks normalization rules, but its still very common) is to store the active account balance (and aged balance) on the master record, as well as maintain a transactions file. The period end then double checks that the account/transactions reconciliation is still correct.

does this makes sense?

 

[jasn_78]

Gemma, totally agree with you. However I am working with a db that my company didnt develop (Just trying to write reports from it) it was developed in firebird and trust me when I say that dba isnt going to rework the database

Unfortunatley he only stores a total balance which isnt really the same as amount outstanding as outstanding amount is for the balance before the previous month minus any payments received in the current period.

any ideas?

 

[jasn_78]

Gemma thanks for looking at it but i figured it out in the end just changed the join type on a query of mine thanks again