Sum up repeating records from a query

[KLAU]

Using Access 97, I am trying to create a report that allows the user
to view products and their quanities sold by a select year. The user
will input a year, my query will run, then an Excel report will open
and return all the listings of the products sold that year and the
quantity totals. See example 1 below:

Example 1
Year: 2003
Product Name Quantity Sold
Carrots 15
Carrots 20
Carrots 20
Tomatoes 10
Tomatoes 15

Now, I want to try to SUM up the quanities and get a total for EACH
product for that year. See example 2 below:

Example 2
Year: 2003
Product Name Quantity Sold
Carrots 55
Tomatoes 25

I have the report generating like the first example, BUT NOW I need
the 2nd example. Any thoughts?

 

[Dale Fye]

You need to do an aggregate query as the source for the report.
Something like

SELECT Year, ProductName, Sum(Quantity) as TotalQuantitiy
FROM yourTable
WHERE Year = 2003
GROUP BY Year, ProductName


--
HTH

Dale Fye


Using Access 97, I am trying to create a report that allows the user
to view products and their quanities sold by a select year. The user
will input a year, my query will run, then an Excel report will open
and return all the listings of the products sold that year and the
quantity totals. See example 1 below:

Example 1
Year: 2003
Product Name Quantity Sold
Carrots 15
Carrots 20
Carrots 20
Tomatoes 10
Tomatoes 15

Now, I want to try to SUM up the quanities and get a total for EACH
product for that year. See example 2 below:

Example 2
Year: 2003
Product Name Quantity Sold
Carrots 55
Tomatoes 25

I have the report generating like the first example, BUT NOW I need
the 2nd example. Any thoughts?