Drawn and Quartered

[GwenH]

The following query displays a year's worth of entries from a table named
Activities. Each loan officer has one record for each of the 12 months in the
year. How can I modify this query so that it gives me the data by quarter? In
other words, I need a result set that contains four records for each loan
officer - one for quarter 1, one for quarter 2, etc.

SELECT activities.employee, activities.officerNumber, Employees.group,
Employees.marketSize, Max(activities.loanPortfolioCI) AS
MaxOfloanPortfolioCI, Max(activities.loanPortfolioCRE) AS
MaxOfloanPortfolioCRE, Sum(activities.newLoans) AS SumOfnewLoans,
Avg(activities.loanTEs_percent) AS AvgOfloanTEs_percent,
Sum(activities.loanTEs) AS SumOfloanTEs, Max(activities.deposits) AS
MaxOfdeposits, Sum(activities.newDeposits) AS SumOfnewDeposits,
Sum(activities.FWM) AS SumOfFWM, Sum(activities.HELOC) AS SumOfHELOC,
Sum(activities.SBA) AS SumOfSBA, Sum(activities.Leasing) AS SumOfLeasing,
Avg(activities.SPH) AS AvgOfSPH, Max(activities.busDevPercent) AS
MaxOfbusDevPercent
FROM activities INNER JOIN Employees ON activities.officerNumber =
Employees.officerNumber
GROUP BY activities.employee, activities.officerNumber, Employees.group,
Employees.marketSize;

Many thanks in advance for your help!

GwenH
Some of my best leading men have been dogs and horses. ~ Elizabeth Taylor

 

[Smartin]

The following query displays a year's worth of entries from a table named
Activities. Each loan officer has one record for each of the 12 months in the
year. How can I modify this query so that it gives me the data by quarter? In
other words, I need a result set that contains four records for each loan
officer - one for quarter 1, one for quarter 2, etc.

SELECT activities.employee, activities.officerNumber, Employees.group,
Employees.marketSize, Max(activities.loanPortfolioCI) AS
MaxOfloanPortfolioCI, Max(activities.loanPortfolioCRE) AS
MaxOfloanPortfolioCRE, Sum(activities.newLoans) AS SumOfnewLoans,
Avg(activities.loanTEs_percent) AS AvgOfloanTEs_percent,
Sum(activities.loanTEs) AS SumOfloanTEs, Max(activities.deposits) AS
MaxOfdeposits, Sum(activities.newDeposits) AS SumOfnewDeposits,
Sum(activities.FWM) AS SumOfFWM, Sum(activities.HELOC) AS SumOfHELOC,
Sum(activities.SBA) AS SumOfSBA, Sum(activities.Leasing) AS SumOfLeasing,
Avg(activities.SPH) AS AvgOfSPH, Max(activities.busDevPercent) AS
MaxOfbusDevPercent
FROM activities INNER JOIN Employees ON activities.officerNumber =
Employees.officerNumber
GROUP BY activities.employee, activities.officerNumber, Employees.group,
Employees.marketSize;

Many thanks in advance for your help!

GwenH
Some of my best leading men have been dogs and horses. ~ Elizabeth Taylor

You must have a field that refers to a date of some sort. Let's say you
call it "activities.ActivityDate".

Then you could add something like this to your GROUP BY clause:

Int(Month(activities.ActivityDate)/4) + 1

If you need to include the year, perhaps

Year(activities.ActivityDate) & '-' &
Int(Month(activities.ActivityDate)/4) + 1