Unconventional Grouping on Report

[dm5085]

I have a report that is pretty basic, there are award types and counts by Manager in the detail section and a subtotal of these counts by Next Level Manager in a footer called NLM. I've been asked to add the awards given by the NLM in the detail section and have a grand total of all in the footer. The query that the report runs from is grouped by Manager. Any ideas on how I should tackle this one?
Thanks in advance!

 

[dhookom]

By "grand total of all in the footer" I assume you mean the Report Footer. Consider creating a query similar to your report's record source except don't group by Manager or Next Level Manager. Just group by the level of detail you want to see in your report footer.

Create a subreport based on your new query and place it in the report footer.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dm5085]

Dhookum, thanks for the reply. What I can't get my head around is how to add the detail record for the NLM in the detail section, I can create the footer. Here are the query results:

NLMName MgrNam Award1 Award2 Award3 Award4
JONES, SCOTT MILLER, JOE 4 0 0 0
JONES, SCOTT SMITH, BOB 1 0 0 0
BARNES, LOU JONES, SCOTT 1 1 0 0

So the way the report runs now with the NLM footer:

MgrName Award1 Award2 Award3 Award4
MILLER, JOE 4 0 0 0
SMITH, BOB 1 0 0 0
Total for Jones, Scott: 5 0 0 0

What I need it to do is this:

MgrName Award1 Award2 Award3 Award4
MILLER, JOE 4 0 0 0
SMITH, BOB 1 0 0 0
JONES, SCOTT 1 0 2 0
Grand Total: 6 0 2 0

The part I'm having trouble with is how to get Scott Jones in there as a detail record when his NLM would be Lou Barnes.

Does this make more sense?

 

[dhookom]

Am I correct in assuming you are grouping by NLMName but since Scott Jones is both an NLMName and a MgrName, he only shows under his NLMName (BARNES, LOU).

If so, I would think you might need a union query that would add records for all NLMNames with the same value in NLMName and MgrName.
SELECT NLMName, MgrName, Award1, Award2, Award3, Award4
FROM tblNames
UNION ALL
SELECT MgrName, MgrName, Award1, Award2, Award3, Award4
FROM tblNames
WHERE MgrName IN (SELECT NLMName FROM tblNames);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dm5085]

Brilliant! Worked like a charm. Thank you so much.