Sum Field A by Field B

  • Thread starter Thread starter Mark1
  • Start date Start date
M

Mark1

I'm new to Access but old to SQL. I want to sum one field
by criteria on another field. In SQL, it would be:

Select sum(dollar) as Dollar
From Table1
Where Region in ('001', '002')

Here, I get the sum of the dollars without Region being
displayed. Just one number is what I want. Not the sums
for regions 001 and 002 but rather the sum of both.
Thanks in advance.
 
You might try the following:

1. Create a new query in design view.

2. When the "Show Table" dialog appears, just click Close.

3. From the View menu, choose SQL View.

4. In the SQL window that appears, paste your SQL.

5. From the View menu, choose Design View to see the graphical eqiuvalent.

6. From the View menu, choose Datasheet View to see the query result.
 
You may need to enter into design view of the query and
select the Totals icon on the toolbar. At that point on
the calculated field, select SUM from the drop down box.
 
The only thing I see is that you are using an alias for the field's sum which is
the same as the fieldname you are summing. I would try a slight modification
since I have problems with using the same name for the field and the alias.

SELECT SUM(Dollars) as TotalDollars
FROM Table1
WHERE Region in ('001','002')
 
Back
Top