counting fields for more than 1 criteria

  • Thread starter Thread starter Jderrig
  • Start date Start date
J

Jderrig

I am working on building a query that I need to count the
number of companies that have (1) >10% Return on Local
Equity and (2) <10% Return on Local Equity.

Is there a way to do this in one query, or even a report
instead of having to run two separate queries?
 
It would help if you posted at least one of the queries.

A sample snippet showing the concept.

SELECT Abs(SUM(SomeField>.1)) as Over10,
Abs(SUM(SomeField<=.1)) as LessThan10
FROM YourTable ...

By the way Less than 10% and More than 10% does leave out anything that is
exactly 10%. A fact you are probably aware of, but it seldom hurts to state the obvious.
 
This is one of the queries that I have. It is a crazy
query, but then again, the whole database is turning out
this way.

Thanks...

SELECT Count([2003 Profitable].LOLAcctNo) AS
CountOfLOLAcctNo, Sum([2003 Profitable].ROLE) AS
SumOfROLE, [2003 Profitable].WorkingCapital, [2003
Profitable].Sales, [2003 Profitable].GrossRevenue, [2003
Profitable].Expenses, [2003 Profitable].LocalSavings,
[2003 Profitable].MemberEquity, [2003
Profitable].LongTermDebt, [2003 Profitable].FixedAssets,
[2003 Profitable].[Salary/BenefitExp], [2003
Profitable].CurrentAssets, [2003
Profitable].CurrentLiabilities, [2003
Profitable].InvOtherOgan
FROM [2002 Profitable] INNER JOIN [2003 Profitable] ON
[2002 Profitable].LOLAcctNo = [2003 Profitable].LOLAcctNo
GROUP BY [2003 Profitable].WorkingCapital, [2003
Profitable].Sales, [2003 Profitable].GrossRevenue, [2003
Profitable].Expenses, [2003 Profitable].LocalSavings,
[2003 Profitable].MemberEquity, [2003
Profitable].LongTermDebt, [2003 Profitable].FixedAssets,
[2003 Profitable].[Salary/BenefitExp], [2003
Profitable].CurrentAssets, [2003
Profitable].CurrentLiabilities, [2003
Profitable].InvOtherOgan
HAVING (((Sum([2003 Profitable].ROLE))<0.1));
-----Original Message-----
It would help if you posted at least one of the queries.

A sample snippet showing the concept.

SELECT Abs(SUM(SomeField>.1)) as Over10,
Abs(SUM(SomeField<=.1)) as LessThan10
FROM YourTable ...

By the way Less than 10% and More than 10% does leave out anything that is
exactly 10%. A fact you are probably aware of, but it
Click to expand...
seldom hurts to state the obvious.
 
Back
Top