MMEX Discussion Forum

Hi. I'm new to SQL, and completely lost in trying to get what I want. Is anyone able to point me in the right direction, please?

I have a single account, which has income (donations) from a number of payees, and expenditure (grant payments) to one recipient (payee). What I would like is the similar to the built-in Payee report, but to only show payees where the TRANSCODE is Deposit.

I've managed to use a sample SQL report to get a list of the transactions, but cannot see how to exclude a specific TRANSCODE, and no idea how to get the chart (or use dates selected by the user).

SQL in use at the moment is as follows. Any help very gratefully received.


select PAYEENAME,CATEGNAME,TRANSCODE,TRANSAMOUNT
from
CHECKINGACCOUNT_V1
join
ACCOUNTLIST_V1 on CHECKINGACCOUNT_V1.ACCOUNTID=ACCOUNTLIST_V1.ACCOUNTID
join
PAYEE_V1 on PAYEE_V1.PAYEEID=CHECKINGACCOUNT_V1.PAYEEID
join
CATEGORY_V1 on CATEGORY_V1.CATEGID=CHECKINGACCOUNT_V1.CATEGID;

rpbeesley wrote:Hi. I'm new to SQL, and completely lost in trying to get what I want. Is anyone able to point me in the right direction, please?...

Did you get this sorted?

You need the WHERE clause.

---
select PAYEENAME,CATEGNAME,TRANSCODE,TRANSAMOUNT
from CHECKINGACCOUNT_V1
join ACCOUNTLIST_V1 on CHECKINGACCOUNT_V1.ACCOUNTID=ACCOUNTLIST_V1.ACCOUNTID
join PAYEE_V1 on PAYEE_V1.PAYEEID=CHECKINGACCOUNT_V1.PAYEEID
join CATEGORY_V1 on CATEGORY_V1.CATEGID=CHECKINGACCOUNT_V1.CATEGID
where CHECKINGACCOUNT_V1.TRANSCODE = 'Deposit';