Summary totals in query

[FJB]

It's me again. Thanks now for your help.

I am trying to create a query which will allow us to determine the
number of filings during a specified period of time (usually monthly).
I would like to be able to view a summary by investigator and group
rather than the individual records.

Investigator 1 is a part of Group A; investigator 2 is also a part of
Group A, but investigator 3 is part of Group B.

I would like to see the following:

Investigator 1 3
Investigator 2 5
Group A 8

Below is the SQL view of the query as it now is constructed.

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

 

[Tom Ellison]

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

It's me again. Thanks now for your help.

I am trying to create a query which will allow us to determine the
number of filings during a specified period of time (usually monthly).
I would like to be able to view a summary by investigator and group
rather than the individual records.

Investigator 1 is a part of Group A; investigator 2 is also a part of
Group A, but investigator 3 is part of Group B.

I would like to see the following:

Investigator 1 3
Investigator 2 5
Group A 8

Below is the SQL view of the query as it now is constructed.

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