asked on Access 2007 - How do I create a cumulative sum for a field in a number of records
I have an SQL database which is the backend for my helpdesk system.
I need to do some reporting and need some advice.
I have a report which will take data from various tables.
I have created a query to show the following
TICKET_ID
BILLING_MINUTES
This returns a number of rows similar to the info below
TICKET_ID BILLING_MINUTES
101100 60
101100 30
101100 30
101101 60
and so on. . . .
I want the query to return a sum for each TICKET_ID such as..
101100 120
101101 60
You advice on how to do this is apprciated.
I need to do some reporting and need some advice.
I have a report which will take data from various tables.
I have created a query to show the following
TICKET_ID
BILLING_MINUTES
This returns a number of rows similar to the info below
TICKET_ID BILLING_MINUTES
101100 60
101100 30
101100 30
101101 60
and so on. . . .
I want the query to return a sum for each TICKET_ID such as..
101100 120
101101 60
You advice on how to do this is apprciated.
Microsoft Access
Last Comment
swan_solutions
select ticket_id , sum(billing_minutes) from mytable group by ticket_id;
The above statement will return the output you want.
The above statement will return the output you want.
yep.. and you just repeated my post
ASKER
Thanks for your replies.
When I do this for the 2 fields it totals them ok.
When I do the same in a larger query (with the same fields in) it does not work.
It sorts by TICKET_ID but several rows return for the same TICKET_ID so it does not display a SUM for BILLING_MINUTES
I have attached the query and the results for you to view.
query.jpg
results.jpg
When I do this for the 2 fields it totals them ok.
When I do the same in a larger query (with the same fields in) it does not work.
It sorts by TICKET_ID but several rows return for the same TICKET_ID so it does not display a SUM for BILLING_MINUTES
I have attached the query and the results for you to view.
query.jpg
results.jpg
the query posted is only good for the info you gave in your original question...
ASKER
To confirm - the sum is being calculated but I only want to show 1 row for each TICKET_ID
How can I stop all rows for each ticket from being returned?
How can I stop all rows for each ticket from being returned?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks - V helpful
select ticket_id, sum(billing_minutes)
from tablex
group by ticket_id