472,139 Members | 1,437 Online

# How do I subtotal and grand total in a query?

Hello,

I am trying to build a query that subtotals the amount of hours worked and earnings made per pay period for each employee. I built a Payroll table with employee name, start time, end time, lunch in, lunch out, hourly rate and added these fields to my query to form new fields with expressions to calculate the total hours worked per day (end time- start time) and the earnings made per day (total hours worked per day x hourly rate)and now I would like to subtotal the hours worked and earnings made per payperiod for each employee.

I put the criteria for the date field between #1/30/2012#and#2/5/2012#, which is the pay period I want to look at, and I tried grouping the sum of my new field expressions (hours worked per day, earnings made per day) and when I try to run the query summing both expressions, a message pops up saying, "subqueries cannot be used in the expression ([total hours]*[hourly rate])". When I try grouping the sum of one expression, for example the earnings made per day expression field, a message pops up saying, "you need to execute a query that does not include the specified expression '([end time]-[start time])'".

How do I subtotal and grand total my hours and earnings for each employee for the specified date range?
Mar 16 '12 #1

You don't do subtotals and grand totals in a query, you do it in the report.

5 20093
Rabbit
12,516 Expert Mod 8TB
You don't do subtotals and grand totals in a query, you do it in the report.
Mar 16 '12 #2
Thank you Rabbit. I will do that.
Mar 19 '12 #3
hvsummer
215 128KB
@Chelle8263: Actually Rabbit is not correct, you can have subtotal in Query.
Using GroupBy/Total Query like this:
Expand|Select|Wrap|Line Numbers
1. SELECT Group, SubGroup, Sum(Qty) as MTD
2. FROM Table1
3. GROUP BY Group, SubGroup
4. ORDER BY Group, SubGroup
5. UNION ALL
6. SELECT Group, "Total "&Group as SubGroup, Sum(Qty) as MTD
7. FROM Table1
8. GROUP BY Group
9. ORDER BY Group, SubGroup;
10.
Nov 23 '15 #4
Rabbit
12,516 Expert Mod 8TB
@hvsummer, I didn't say you couldn't. Rather, I said you don't. The implication being that it's better to handle subtotals and grand totals on the reporting end rather than using a work around solution on the querying side.
Nov 23 '15 #5
NeoPa
32,497 Expert Mod 16PB
@HVSummer.
Rabbit is quite right here.

Please consider, when offering advice, the sense of that advice. Inexperienced people new to Access often ask for things that are not good for them. It is our responsibility as experts to consider this before simply offering answers. There are many things that we know how to do, yet would never offer as advice without a strong warning against using such an approach. It is irresponsible to do otherwise.

If, after some responsible direction from an expert, the OP still requires direction to go a way that they have been warned against, then that's fine. We aren't baby-sitters. Nevertheless we do have a responsibility to give sensible advice where we can. Bad advice is generally worse than no advice at all, so care and consideration must always be shown.
Nov 24 '15 #6