Subtracting One Total From Another on a Different Row in a Query

[FuzzyTom]

Hi,

With help from MrExcel I have the following:

SELECT qryTestUnion.EmployeeName, qryTestUnion.Day, Sum(qryTestUnion.Total) AS SumOfTotal, qryTestUnion.Source
FROM qryTestUnion
GROUP BY qryTestUnion.EmployeeName, qryTestUnion.Day, qryTestUnion.Source;

the result is


<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>queryofqryTestUnion</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>EmployeeName</TH><TH bgColor=#c0c0c0 borderColor=#000000>Day</TH><TH bgColor=#c0c0c0 borderColor=#000000>SumOfTotal</TH><TH bgColor=#c0c0c0 borderColor=#000000>Source</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5>DAVID DAVIA</TD><TD borderColor=#d0d7e5 align=right>4/1/2011</TD><TD borderColor=#d0d7e5 align=right>8.00</TD><TD borderColor=#d0d7e5>DailyActivity</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>David Davia</TD><TD borderColor=#d0d7e5 align=right>4/1/2011</TD><TD borderColor=#d0d7e5 align=right>8.50</TD><TD borderColor=#d0d7e5>TimeSheet</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>DAVID DAVIA</TD><TD borderColor=#d0d7e5 align=right>4/4/2011</TD><TD borderColor=#d0d7e5 align=right>8.00</TD><TD borderColor=#d0d7e5>DailyActivity</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>David Davia</TD><TD borderColor=#d0d7e5 align=right>4/4/2011</TD><TD borderColor=#d0d7e5 align=right>7.50</TD><TD borderColor=#d0d7e5>TimeSheet</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>DAVID DAVIA</TD><TD borderColor=#d0d7e5 align=right>4/5/2011</TD><TD borderColor=#d0d7e5 align=right>8.00</TD><TD borderColor=#d0d7e5>DailyActivity</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>David Davia</TD><TD borderColor=#d0d7e5 align=right>4/5/2011</TD><TD borderColor=#d0d7e5 align=right>8.00</TD><TD borderColor=#d0d7e5>TimeSheet</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>DAVID DAVIA</TD><TD borderColor=#d0d7e5 align=right>4/6/2011</TD><TD borderColor=#d0d7e5 align=right>8.00</TD><TD borderColor=#d0d7e5>DailyActivity</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>David Davia</TD><TD borderColor=#d0d7e5 align=right>4/6/2011</TD><TD borderColor=#d0d7e5 align=right>8.00</TD><TD borderColor=#d0d7e5>TimeSheet</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>DAVID DAVIA</TD><TD borderColor=#d0d7e5 align=right>4/7/2011</TD><TD borderColor=#d0d7e5 align=right>8.00</TD><TD borderColor=#d0d7e5>DailyActivity</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>David Davia</TD><TD borderColor=#d0d7e5 align=right>4/7/2011</TD><TD borderColor=#d0d7e5 align=right>8.00</TD><TD borderColor=#d0d7e5>TimeSheet</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>DAVID DAVIA</TD><TD borderColor=#d0d7e5 align=right>4/8/2011</TD><TD borderColor=#d0d7e5 align=right>8.00</TD><TD borderColor=#d0d7e5>DailyActivity</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>David Davia</TD><TD borderColor=#d0d7e5 align=right>4/8/2011</TD><TD borderColor=#d0d7e5 align=right>8.00</TD><TD borderColor=#d0d7e5>TimeSheet</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>DAVID DAVIA</TD><TD borderColor=#d0d7e5 align=right>4/11/2011</TD><TD borderColor=#d0d7e5 align=right>1.00</TD><TD borderColor=#d0d7e5>DailyActivity</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>David Davia</TD><TD borderColor=#d0d7e5 align=right>4/11/2011</TD><TD borderColor=#d0d7e5 align=right>1.00</TD><TD borderColor=#d0d7e5>TimeSheet</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>DAVID DAVIA</TD><TD borderColor=#d0d7e5 align=right>4/15/2011</TD><TD borderColor=#d0d7e5 align=right>6.00</TD><TD borderColor=#d0d7e5>DailyActivity</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>DAVID DAVIA</TD><TD borderColor=#d0d7e5 align=right>4/15/2011</TD><TD borderColor=#d0d7e5 align=right>6.00</TD><TD borderColor=#d0d7e5>TimeSheet</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

I would like to subtract the SumofTotal(DailyActivity) from SumofTotal(TimeSheet) for each day.

I have tried to include a autonumber in my underlying table but as you might imagine there are too many entries that fall on one day so that the numbers are no longer sequential after the totals are summed such that we have totals for each day.

Any suggestions how I can write a SQL or VBA code to run through the query such that it moves all of the entries of the same day onto one row so that I can then write an expression to give me the difference I mentioned earlier.

Thanks for any help or pointers as to how to solve this little problem.

Tom.

 

[Joe4]

You could do another Aggregate Query based on the one you have posted, and make use of an IIF statement. Something like this maybe:

SELECT queryofqryTestUnion.EmployeeName, queryofqryTestUnion.Day, Sum(IIf([queryofqryTestUnion]![Source]="DailyActivity",[queryofqryTestUnion]![SumOfTotal],0)-IIf([queryofqryTestUnion]![Source]="TimeSheet",[queryofqryTestUnion]![SumOfTotal],0)) AS DailyTotal
FROM queryofqryTestUnion
GROUP BY queryofqryTestUnion.EmployeeName, queryofqryTestUnion.Day;

 

[FuzzyTom]

Thanks again Joe4 .... it is the old rule of 'KISS', keep it short and simple, something I have to begin to observe. Well thanks for your help and elegant solutions.
Tom.