Calculating Totals per Item

[PercyPercy]

For some reason I can't write the proper query to get a simple total from a single table. Here is the basic table design:

Transaction ID___Product ID___ Transaction Date____Transaction Quantity
1______________Product A____6/12/2006__________200
2______________Product B____6/12/2006__________500
3______________Product C____6/14/2006__________100
4______________Product B____6/15/2006__________200
5______________Product C____6/16/2006__________300
6______________Product A____6/17/2006__________500

I'm trying to get the total transactions quantity for each product so that I could end up with a form or a report that would show:

Product ID_____ Transaction Quantity Total
Product A______700
Product B______700
Product C______400

... and so on for each item.

What would actually be entered in the query? I'm assuming I would have four columns in the query:

transaction id____product number____transaction qty____expression

Then I would use the SUM feature so the "group by" row appears.

Then I would build the expression above in an expression column. If that's correct, could you give me the expression typed "exactly" as you think it would appear? I'm getting syntax errors when I'm trying it.

And then, once I have the expression built, what should be the "group by" selection under each of the columns?

Sorry for being so ignorant. The rest of my data base works great... but I'm just not getting this one thing!

Thanks anyone who can help me!

 

[Matt Greatorex]

You're trying to use the group by clause to include a field where, by the looks of it, all the values are unique (transaction id). You probably only want something like

SELECT [Product ID], Sum([Transaction Quantity])
FROM tablename
GROUP BY [Product ID];

 

[PercyPercy]

I'm such a novice, I'm not quite understanding what you mean by:

SELECT [Product ID], Sum([Transaction Quantity])
FROM tablename
GROUP BY [Product ID];

So do I use the SUM function -- so the "group by" appears on the "Total" row?

I guess I just don't know what to type -- and where it should go!

I don't know if you can give me a step-by-step instruction or not. Any help is appreciated though...

 

[RV]

So do I use the SUM function -- so the "group by" appears on the "Total" row?

You need to use a GROUP BY whenever you're using aggregate functions, such as SUM, MIN, MAX.
GROUP BY groups your calculations.
So, as per Matt's solution, you'll end up having exactly what you need.

Read up a bit in Access Help on GROUP BY and aggregate functions.

RV

 

[B4Geyer]

Sounds like you're also asking where the SELECT statement would go... ?
Click on SQL view in your query (same button that lets you switch between Design and Datasheet view) and replace it with the one you got here. HTH