calculate sum in query

[Georges Heinesch]

Hi.

I need to setup a query, which calculates the sum.

This is the table:

10.02.2003 Bananas 20 USD
12.02.2003 Apple 10 USD
13.02.2003 Bananas 25 USD
16.02.2003 Pinaples 20 USD
19.02.2003 Bananas 30 USD

I need the total amount of all babanas (= 75 USD).

How can I do this?

Thanks.

 

[Al Campagna]

George,
Create a Totals query (View/Totals)
Sort by ProductID or Product as desired.
Set up the total query as follows...

ProductID Product Price
GroupBy GroupBy Sum
Ascending
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

You cannot include the date field in your totals query because group on will
give:

Group Group Sum
Like "Bananas"

Date Produce Cost
10/02/2003 Bananas 20
13/02/2003 Bananas 25
19/02/2003 Bananas 30

Like "Bananas" Goes in the Criteria row on the Produce to select only Bananas

If you delete the date column you will get:

Produce Cost
Group Sum
Like "Bananas"

Bananas 75

As Al says, you would be better off with a product ID which would then group.

HTH Mike B
--
An Engineers Prayer:
At the very end of the day,
when all else fails,
you have tried all,
you have shouted at and blamed the innocent,
and asked everyone you know,

READ THE INSTRUCTION MANUAL.

 

[Guest]

PMJI. The date can be included by using a subquery, though I can't imagine a
situation in which anyone would actually want to e.g.

SELECT TransactionDate, Product, Amount,
(SELECT SUM(Amount)
FROM Sales AS S2
WHERE S2.Product = S1.Product) AS TotalAmount
FROM Sales AS S1;

The TotalAmount value would be the same for each row of the same product of
course. What's more likely is that someone might want a running sum per
product:

SELECT TransactionDate, Product, Amount,
(SELECT SUM(Amount)
FROM Sales AS S2
WHERE S2.Product = S1.Product
AND S2.TransactionDate <= S1.TransactionDate) AS RunningSum
FROM Sales AS S1
ORDER BY Product, TransactionDate;

Though even this would, like the first, be better done in a report.

BTW there's no need to use the LIKE operator to restrict the query on the
Product column, a simple equality operation will do, so just putting
"Bananas" in the criteria row of the Product column in query design view
would be enough.

Ken Sheridan
Stafford, England