CODEWITHSUNDEEP
ms-access
bd528
bd528

Reputation: 886

Return records matching count criteria

The query below returns grouped results based on a count :-

SELECT c.CUSTOMER_NAME, Count(q.DATE_QUOTE_REQUESTED) AS CountOfDATE_QUOTE_REQUESTED
FROM TBLQUOTESNEW q LEFT JOIN TBLCUSTOMERSNEW c ON q.CUSTOMER_ID = c.CUSTOMERID
GROUP BY c.CUSTOMER_NAME
HAVING (((Count(q.DATE_QUOTE_REQUESTED))>4))
ORDER BY c.CUSTOMER_NAME;

How can I view the results ungrouped, but continuing to factor in the count criteria of >4?

Upvotes: 0

Views: 44

Answers (1)

Rajani B
Rajani B

Reputation: 203

This query is just for returning customer table wihtout quotes... if you need quotes data also try joining this result with quotes table...

select * from TBLCUSTOMERSNEW cc where cc.CUSTOMER_NAME in 
 (SELECT c.CUSTOMER_NAME FROM TBLQUOTESNEW q LEFT JOIN TBLCUSTOMERSNEW c 
  ON q.CUSTOMER_ID = c.CUSTOMERID
  GROUP BY c.CUSTOMER_NAME
  HAVING (((Count(q.DATE_QUOTE_REQUESTED))>4))
 )
order by cc.CUSTOMER_NAME;

Upvotes: 1

Related Questions