How do I subtotal and grand total in a query?

[Chelle8263]

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?

[Rabbit]

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

[Chelle8263]

Thank you Rabbit. I will do that.

[hvsummer]

@Chelle8263: Actually Rabbit is not correct, you can have subtotal in Query.
Using GroupBy/Total Query like this:

SELECT Group, SubGroup, Sum(Qty) as MTD
FROM Table1
GROUP BY Group, SubGroup
ORDER BY Group, SubGroup
UNION ALL
SELECT Group, "Total "&Group as SubGroup, Sum(Qty) as MTD
FROM Table1
GROUP BY Group
ORDER BY Group, SubGroup;
 

[Rabbit]

@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.

[NeoPa]

@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.