Solved

How to subtotal a field in a Access query which is being counted?

Posted on 2006-11-06
2
179 Views
Last Modified: 2006-11-18
In the acess query builder, I set one of the fields to count function, so I would get the total number of alarms each day for each pump.  My current result from the query look like the following:

Equipment -- Date -- Number of Alarms
=========================
PUMP10 -- 10/24/06 -- 10
PUMP10 -- 10/25/06 -- 5
PUMP10 -- 10/22/06 -- 5
PUMP2 -- 10/24/06 -- 10
PUMP2 -- 10/20/06 -- 10
PUMP2 -- 10/21/06 -- 10
PUMP24 -- 10/19/06 -- 12
PUMP24 -- 10/12/06 -- 10

I would like to have a subtotal show up for each pump.  PUMP10 = 20 total, PUMP2 = 30 total, etc.  Then I would like to sort them so the pump with the largest combined total of alarms shows up first in the list (sort descending).  The report will show the pump with the largest number of alarms first with a breakdown of how many alarms occurred for that pump and on what day.
I am not sure if I can do this in a query or if I need to do this in a report form.  I am not sure if I need a second query so that it will sort by total alarms for each pump.  

Your help is very much appreciated.

0
Comment
Question by:r_may
2 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
Comment Utility
Something like this perhaps , note I dont know your query so I dont know how u do your count

SELECT Equipment, [Date], YourCountField
FROM mytable
GROUP BY Equipment, [Date]
UNION ALL
SELECT Equipment, 'Total' DSUM("*","mytable","Equipment='" & A.Equipment & "'") AS Total
FROM Mytable as A
ORDER BY 1,2
0
 

Author Comment

by:r_may
Comment Utility
Thanks. I will give this a try.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now