Dear FJB:

First, I have rewritten your query as you gave it but using aliases and with
some indentation.  This is for my ease of discovering what you have so far.

SELECT F.[FIU Investigator], F.[SAR Filed Date], F.[Relationship Name],
I.Group
 FROM [SAR Filings] F
   INNER JOIN [Investigator-Group Table] I ON F.[FIU Investigator] =
I.Investigator
 GROUP BY F.[FIU Investigator], F.[SAR Filed Date], F.[Relationship Name],
I.Group
 HAVING F.[SAR Filed Date] Between [Start Date] And [End Date]
 ORDER BY I.Group;

You cannot have a simple query that shows both the individual investigators,
dates, and relationships AND gives this total.  You will need to combine two
queries if you want this in a query.  Typically, this is done in a Report or
other facility that can give you "level breaks" with subtotals.

Let's start with a query to give only the sub-total lines:

SELECT I.Group, COUNT(*) AS Investigations
 FROM [SAR Filings] F
   INNER JOIN [Investigator-Group Table] I ON F.[FIU Investigator] =
I.Investigator
 GROUP BY I.Group
 WHERE F.[SAR Filed Date] Between [Start Date] And [End Date]
 ORDER BY I.Group;

Does this give you a result like the third line of your example?

To achieve similar results for each Investigator:

SELECT I.Group, F.[FIU Investigator] AS Investigator, COUNT(*) AS
Investigations
 FROM [SAR Filings] F
   INNER JOIN [Investigator-Group Table] I ON F.[FIU Investigator] =
I.Investigator
 GROUP BY I.Group, F.[FIU Investigator],
 WHERE F.[SAR Filed Date] Between [Start Date] And [End Date]
 ORDER BY I.Group;

In order to combine these into one query, you would need a UNION.  For this
to work, both must have the same columns, so I add an empty column in the
first one for Investigator.

SELECT I.Group, 1 AS SortOrder, '' AS Investigator, COUNT(*) AS
Investigations
 FROM [SAR Filings] F
   INNER JOIN [Investigator-Group Table] I ON F.[FIU Investigator] =
I.Investigator
 GROUP BY I.Group
 WHERE F.[SAR Filed Date] Between [Start Date] And [End Date]
UNION
SELECT I.Group, 0 AS SortOrder, F.[FIU Investigator] AS Investigator,
COUNT(*) AS Investigations
 FROM [SAR Filings] F
   INNER JOIN [Investigator-Group Table] I ON F.[FIU Investigator] =
I.Investigator
 GROUP BY I.Group, F.[FIU Investigator],
 WHERE F.[SAR Filed Date] Between [Start Date] And [End Date]
 ORDER BY Group, SortOrder, Investigator;

Does this help?  Does it come close to what you were wanting?

It is rarely necessary, or helpful, to put this in a query as shown.  If you
use a report, you can provide the sub-totals easily using only the second
query shown above.  But techniques like ths are available when necessary.

Tom Ellison