I have searched through the news groups and found many threads close to
what I want but cannot get any of them to work...
I have a table with expenses and invoices, what I want is a bar chart
with value on the y-axis, time on the x-axis and two series...
cumulative expenses and cumulative invoices so I can compare them
against each other against time.
What I have tried to do is create a runing sum column using the SQL
code as follows...
SELECT qryAllOut.mKey AS [Key], Sum(qryAllOut.mAmount) AS
CumulativeOut, DSum("[mAmount]","[qryAllOut]","[InOutQuote] = 'Out' And
[mKey]<=" & [Key] & "") AS RsumOut,
DSum("[mAmount]","[qryAllOut]","[InOutQuote] = 'In' And [mKey]<=" &
[Key] & "") AS RsumIn, qryAllOut.mDate
FROM qryAllOut
GROUP BY qryAllOut.mKey, qryAllOut.mDate;
The gives me a column of cumulative expenses going out, and a
cumulative invoices going out. However this table looks something like
this...
mKey SumIn SumOut Date In/Out
-------------------------------------------
1 1000 31/03/2006 In
2 2000 28/04/2006 In
3 4000 05/06/2006 In
4 4000 50 10/03/2006 Out
5 4000 150 15/03/2006 Out
etc.
Firstly is their any way to stop the SumIn column registering a value
when the In/Out value is Out? Otherwise this screws up my values when
i reorganise by date.
Is it possible to do running total by data and not using the unique
key.
Is it possible to not use SQL all together and create sum kind of
function in VBA to do it (I hate SQL and am much more familar with VBA)
My only other problem is that when I try to create a graph out of what
I have, it will not let me assign the Sum of the columns SumIn and
SumOut only the count... this is because the columns are defined as
text and not numeric values - however SQL will not allow to change
these column as it results in an error in my code.
Please help as I am pulling my hair out here! SO frustrating!
Cheers,
James Hallam
