Sum up repeating records from a query

  • Thread starter Thread starter KLAU
  • Start date Start date
K

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?
 
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?
 
Back
Top