error in query to get balance

Question

i have a Cash table with fields: doc_num,narration,date,creditor,debtor now i want to show sum(creditor-debtor) as balance for each rows.also show previous balance like this :

--------------------------------------------------------------------------------------
doc_num-------date----------narration------------credit-------debtor---balance
--------------------------------------------------------------------------------------
                         previous balance                                 2000    
 1       2014/06/10        remit             5000          0              5000
 2       2014/06/12        bar                  0          2000           3000
 3       2014/06/15        remit             1000          0              4000

this is my query :

SELECT  ''                  AS docnum,
        ''                  AS c_date,
        'previous balance'  AS narration,
        " "                 AS creditor,
        " "                 AS debtor,
        Sum(creditor-debtor),
        "c_date< ' " &  2014/06/10  & "'" AS Balance 
FROM    Cash 
UNION 
SELECT  doc_num,
        c_date,
        narration,
        creditor,
        debtor,
        Sum(creditor-debtor),
        "doc_num<=" & doc_num AS Balance 
FROM    Cash
WHERE   (((Cash.c_date) Between ("2014/06/10") And ("2014/06/15")))
ORDER BY docnum;

i faced this err: you tried to execute query that dose not include the specified expression 'doc_num' as part of an aggregate i use this query in access(mdb) what's a correct query? thanks

Answer 1

Perhaps this: the '' and " " are throwing me off a bit.

SELECT  ''                  AS docnum,
        ''                  AS c_date,
        'previous balance'  AS narration,
        " "                 AS creditor,
        " "                 AS debtor,
        Sum(creditor-debtor),
        "c_date< ' " &  2014/06/10  & "'" AS Balance 
FROM    Cash 
Group by '','','previous balance', " ", " ", "c_date< ' " &  2014/06/10  & "'"
UNION 
SELECT  doc_num,
        c_date,
        narration,
        creditor,
        debtor,
        Sum(creditor-debtor),
        "doc_num<=" & doc_num AS Balance 
FROM    Cash
WHERE   (((Cash.c_date) Between ("2014/06/10") And ("2014/06/15")))
GROUP BY doc_num,
        c_date,
        narration,
        creditor,
        debtor,
        "doc_num<=" & doc_num 
ORDER BY docnum;