Page 1 of 1

SQL Query - need MS Access wizard! COUNT & GROUP

Posted: Thu Mar 19, 2009 6:16 am
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

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

Posted: Fri Mar 20, 2009 3:11 pm
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;
 

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

Posted: Mon Mar 23, 2009 10:10 am
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

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

Posted: Mon Mar 23, 2009 1:50 pm
by califdon
Do post it separately, please. That way, others will see it (in the proper forum and with its own Subject).