snhandle
asked on
MS access
I want to do Running Sum in Query2 but it is not working please if someone can look at it and fix it. Attach is the database.
Thanks
test.mdb
Thanks
test.mdb
Can you give example of what you want to see, based on posted data?
ASKER
EmpAlias SumOfSumOfTotal RunTot
01-Jul-12 6,000.00 6000
02-Jul-12 500.00 6500
03-Jul-12 (1,200.00) 5,300.00
09-Jul-12 (10,927.40) -5627.4
I want to see the query2 result like the above result if I select the date range from July 1 through July 9th.
Thanks
01-Jul-12 6,000.00 6000
02-Jul-12 500.00 6500
03-Jul-12 (1,200.00) 5,300.00
09-Jul-12 (10,927.40) -5627.4
I want to see the query2 result like the above result if I select the date range from July 1 through July 9th.
Thanks
Try this, one query:
SELECT a.Date AS EmAlias, Sum(nz([dr],0))-Sum(nz([cr ],0)) AS SumTotal, DSum("nz([dr],0)","[base Table]","[date] <= #" & [a.date] & "#")-DSum("nz([cr],0)","[b ase Table]","[date] <= #" & [a.date] & "#") AS unTot
FROM [Base Table] AS a
GROUP BY a.Date;
SELECT a.Date AS EmAlias, Sum(nz([dr],0))-Sum(nz([cr
FROM [Base Table] AS a
GROUP BY a.Date;
For the parens:
SELECT a.Date AS EmpAlias, Format(Sum(nz([dr],0))-Sum (nz([cr],0 )),"0;(0)" ) AS SumTotal, DSum("nz([dr],0)","[base Table]","[date] <= #" & [a.date] & "#")-DSum("nz([cr],0)","[b ase Table]","[date] <= #" & [a.date] & "#") AS RunTot
FROM [Base Table] AS a
GROUP BY a.Date
HAVING (((a.Date) Between [start date] And [end date]));
SELECT a.Date AS EmpAlias, Format(Sum(nz([dr],0))-Sum
FROM [Base Table] AS a
GROUP BY a.Date
HAVING (((a.Date) Between [start date] And [end date]));
Can I ask why you want to do this in a query?
If you are going to use this in a report, then create an extra textbox in the report, set it's Control Source to the appropriate field, then set the Running Sum property (Data tab) to "Over all" or "Over group".
If you are going to use this in a report, then create an extra textbox in the report, set it's Control Source to the appropriate field, then set the Running Sum property (Data tab) to "Over all" or "Over group".
ASKER
I want to do this in query because I want to get only negative lines in the query, and in reports if I do runningsum then if I want to show only negative lines on the report I can not do that, because then it leave blank spaces.
Did you try the solution I gave you to that problem. I posted a sample database for you to look at.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
good