Nested Queries

[CGSB]

I am having problems making the following query work the way I intend it to work:

SELECT serie_no AS sn, STR((SELECT COUNT(standard_id) FROM standard WHERE is_pdf=True AND is_current=True AND serie_no = sn))+'/'+STR((SELECT COUNT(standard_id) FROM standard WHERE is_current=True AND serie_no = sn)) AS serie_pdfs
FROM standard
GROUP BY serie_no;

My intention was to display the amount of records that have is_pdf set to true for each serie_no.

However, when I run this in Access, I get prompted for sn and the total is the same for each serie_no.

If you need further information in order to help me, please let me know.

Thanks in advance

 

[PHV]

SELECT serie_no AS sn, Sum(IIf(is_pdf AND is_current,1,0)) & '/' & Sum(IIf(is_current,1,0)) AS serie_pdfs
FROM standard
GROUP BY serie_no

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