SQL Query - need MS Access wizard! COUNT & GROUP

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

SQL Query - need MS Access wizard! COUNT & GROUP

Post by simonmlewis »

Hello - thanks for opening this question. I am raising this on behalf of a friend who is working in MS Access.

He is trying to get a result of three columns: Fee Earning (containing their grouped name), File summary (containing the amount of times a "no" has been entered), and Letterhead (containing the amount of time a "no" has been entered there).

Code: Select all

SELECT matters.FeeEarner, 
Count(matters.FileSummary) AS FileSummary,
Count(matters.Letterhead) AS Letterhead
FROM matters
WHERE matters.FileSummary ='no' OR
WHERE matters.FileSummary ='no'
GROUP BY matters.feeEarner;
 
So far, the result is a Grouped list of FeeEarners, and a Count of number of rows - period! So whether one column has a YES and two NOs, it produces a result of "3".

If the 'FeeEarning' has three NOs in fileSummary and two YESs in Letterhead, the result needs to be:
FeeEarningName - 3, 2.

Hope someone can help.

Simon
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: SQL Query - need MS Access wizard! COUNT & GROUP

Post by andyhoneycutt »

You'll likely want to execute two separate queries, a count for each -- you'll need to group by each independently. I'd try:

Code: Select all

 
// Query 1:
SELECT matters.FeeEarner, Count(matters.FileSummary) AS FileSummary
FROM matters
WHERE matters.FileSummary ='no'
GROUP BY matters.FeeEarner;
 
// Query 2:
SELECT matters.FeeEarner, Count(matters.Letterhead) AS Letterhead
FROM matters
WHERE matters.Letterhead ='no'
GROUP BY matters.FeeEarner;
 
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: SQL Query - need MS Access wizard! COUNT & GROUP

Post by simonmlewis »

Thank you.

I'm now in need of some support with LDAP and PHP if you can help, if not, I will post elsewhere.

Cheers
Simon
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: SQL Query - need MS Access wizard! COUNT & GROUP

Post by califdon »

Do post it separately, please. That way, others will see it (in the proper forum and with its own Subject).
Post Reply