Create subtotals in a query

[Heeeeelp]

Hi Everyone,

I would appreciate some help on a problem I'm having with a query. The query displays case number, description, amount of transacation, etc. One case can be displayed in one or more rows if more than one payment was processed for the case.

For example

Case ID Payment Amt Description
123 25.00 Seminar
123 55.00 Seminar
145 75.00 Pens

I would like for the above to be displayed with one total for each case.

123 50.00 Semimar
145 75.00 Pens

I would greatly appreciate some help with this.

Kind Regards,
Tess

 

[traingamer]

You can use a report to return this data a lot more easily than getting it directly from a query.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[MajP]

I think you mean your data to look like this

caseID SumOfpayment description
123 $80.00 Seminar
145 $75.00 Pens

where the sum of 123 is 80 for seminar not 50$

SELECT tblPayment.caseID, Sum(tblPayment.payment) AS SumOfpayment, tblPayment.description
FROM tblPayment
GROUP BY tblPayment.caseID, tblPayment.description;

To do this in the query designer select your fields and then click on the Summation symbol (Sigma), this will make a Group by query. In the payment field change the "Group By" to "Sum".

From the help file on "aggregate queries":

In the Database window, click Queries under Objects, click the query you want to open, and then click Design on the database window toolbar.
Be sure the tables, views, or functions you want to aggregate are already present in the Diagram pane.
Right-click the background of the Diagram pane, then choose Group By from the shortcut menu. The Query Designer adds a Group By column to the grid in the Grid pane.
Add the column you want to aggregate to the Grid pane. Be sure that the column is marked for output. The Query Designer automatically assigns a column alias to the column you are summarizing. You can replace this alias with a more meaningful one.
In the Group By grid column, select the appropriate aggregate function, such as: Sum, Avg, Min, Max, Count. If you want to aggregate only unique rows in the result set, choose an aggregate function with the DISTINCT options, such as Min Distinct. Do not choose Group By, Expression, or Where, because those options do not apply when you are aggregating all rows.

 

[traingamer]

Ignore my earlier answer. I misread the question.


Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[kjv1611]

Heeeeelp,

I'm still trying to get past your name!

I love it! That's just hilarious!

--

"If to err is human, then I must be some kind of human!" -Me