Sum $$ by date

[Schon731]

Probably easy but I need to add all of the negative margin for each date. So basically I want the output to show one line for 3/1/2012 and a total of (3.32) and one line for 3/2/2012 to show (224.15) and on for each date in the table. Thanks in advance.

Store Number	TX-Date Filled	Margin	Total Due	TX-Acq Cost
102	3/1/2012	($2.80)	$28.98	$31.78
102	3/1/2012	($0.52)	$17.26	$17.78
102	3/2/2012	($169.93)	$19.00	$188.93
102	3/2/2012	($29.90)	$31.71	$61.61
102	3/2/2012	($13.82)	$3.98	$17.80
102	3/2/2012	($7.95)	$139.21	$147.16
102	3/2/2012	($2.55)	$35.55	$38.10

[pbaldy]

Start a new query on that table and select the date and margin fields. Click on the Totals icon in the toolbar. Change Group By to Sum on the margin field and run the query.

[Schon731]

Start a new query on that table and select the date and margin fields. Click on the Totals icon in the toolbar. Change Group By to Sum on the margin field and run the query.

Ok its a little more complicated. The margin field has both positive and negative values and I only want the negative so I had <0 in the Criteria. With that the totals doesnt work, with it blank it does but it totals everything so does that mean I need to first create a query that pulls the negatives, then do the totals so I need 2 queries to accomplish this or can I do it in one?

[pbaldy]

Try

SELECT DateField, Sum(IIf(Margin < 0, Margin, 0)) AS TotalNegMargin
FROM TableName
GROUP BY DateField