Query monthly summed account transactions (Simply Accounting)

[djowett]

I am querying a 2008 Simply Accounting mysql database with Access. I want to
build a simple monthly query arranged by account number showing the sum of
all transactions on each account for a particular month.

My problem is that the only query I can come up with leaves out accounts
that are not null but have no transactions in that month.

Here is an example:

SELECT [taccount.lId]/10000 AS [Account #], taccount.sName AS [Account
Name], Sum(Nz([dAmount],0)) AS December
FROM (taccount LEFT JOIN tjentact ON taccount.lId = tjentact.lAcctId) LEFT
JOIN tjourent ON tjentact.lJEntId = tjourent.lId
WHERE (((tjourent.dtJourDate)>=#12/1/2008# And
(tjourent.dtJourDate)<=#12/31/2008#)) OR (((tjourent.dtJourDate) Is Null))
GROUP BY [taccount.lId]/10000, taccount.sName;


I know my issue is that the WHERE clause is excluding accounts that have no
transactions, but I can't figure out how to get around this. Also might have
an issue with my joins. My intention is to import a monthly summary into
Excel to use to build reports/etc.

DJ

 

[Ken Sheridan]

You are correct about the WHERE clause being the problem; a restriction on a
column in a table on the RIGHT side of a LEFT OUTER JOIN will in effect turn
it into an INNER JOIN. I think you'll need to do it in two stages.

1. Create a query on the tjourent and tjentact tables, grouping on the
account ID, restricting it by the date range and summing the amount. This
will be an INNER JOIN.

2. LEFT JOIN the taccount table to the above query on the account ID, using
the NZ function to return a zero in place of the NULL sums where there are no
transactions for the account.

An alternative, which could be done in a single query, would be to put the
first query as a subquery in the SELECT clause of a query on the taccount
table, correlating it with the outer query on the account ID.

Ken Sheridan
Stafford, England