SQL Statement: Query Bulider - Count Fuction Issue 1

[krissrites]

Hi there,

I've been battling with this for awhile now. I'm trying to count all of the records in a field that share the same name.

I'm trying to filter the results using a field that I don't want to see. I also don't want to the field to disrupt a count of the unique values that I'm trying to arrive at.

For example, lets say I have a table showing Salespersons and a history of their transaction totals.

Salesperson Transactions
John 25,000
Jen 30,000
John 15,000
Jen 25,000

If I use query builder and get to the following statement:

SELECT tblTransactions.Salesperson,
Count(tblTransactions.Salesperson) AS CountOfSalesperson
FROM tblTransactions
GROUP BY tblTransactions.Salesperson;

I would get the result:

Salesperson CountOfSalesperson
John 2
Jen 2

But what if I wanted the exact same format as above (showing only Salesperson and CountOfSalesperson) but wanted only those results where 'Transaction' is > 20,000?

Here's what I have:

SELECT tblTransactions.Salesperson, Count(tblTransactions.Salesperson) AS CountOfSalesperson
FROM tblTransactions
GROUP BY tblTransactions.Salesperson, tblTransactions.Transactions
HAVING (((tblTransactions.Transactions)>20000));

Which returns:

Salesperson CountOfSalesperson
John 1
Jen 1
Jen 1

I need the result:

Salesperson CountOfSalesperson
John 1
Jen 2

Has anyone run into this before?

 

[PHV]

SELECT Salesperson, Count(*) AS CountOfSalesperson
FROM tblTransactions
WHERE Transactions>20000
GROUP BY Salesperson

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[krissrites]

Yes, very simple and works perfectly. I was using Query builder and was trying all sorts of things, but that's exactly what I need.

Thanks so much!!!