Counting Records with Criteria

[KCangelstar]

In my database, I am trying to count records from a [Stratum] field that meet the criteria of "[Days}<42". Below is my SQL query code.

SELECT DISTINCTROW WARes016.Stratum, Count(([WA Res 016 Final Selection].[Days])) AS CountOfDays
FROM WARes016 INNER JOIN [WA Res 016 Final Selection] ON WARes016.LRS = [WA Res 016 Final Selection].LRS
GROUP BY WARes016.Stratum, [Days]<42
ORDER BY WARes016.Stratum;

This code when run breaks the count into two groups:a count of [Days] per Stratum that meet my criteria and a count of records that don't.

How do I alter the code to only show the count of records that meet my criteria? Or am I going about this all wrong? I am really new at this.

I would appreciate any feedback!

Thank you!

[Killer42]

I'm going to move this to the Access forum where you'll find much more experience in this area. However, I think that the HAVING clause may be what you need to use.

[KCangelstar]

Thanks Killer

[ADezii]

In my database, I am trying to count records from a [Stratum] field that meet the criteria of "[Days}<42". Below is my SQL query code.

SELECT DISTINCTROW WARes016.Stratum, Count(([WA Res 016 Final Selection].[Days])) AS CountOfDays
FROM WARes016 INNER JOIN [WA Res 016 Final Selection] ON WARes016.LRS = [WA Res 016 Final Selection].LRS
GROUP BY WARes016.Stratum, [Days]<42
ORDER BY WARes016.Stratum;

This code when run breaks the count into two groups:a count of [Days] per Stratum that meet my criteria and a count of records that don't.

How do I alter the code to only show the count of records that meet my criteria? Or am I going about this all wrong? I am really new at this.

I would appreciate any feedback!

Thank you!

I assuming that your Days criteria is not applied to the overall Groupings. If it were, you would need a HAVING Clause. Let me know how this works - SQL is not my strong point.

SELECT DISTINCTROW WARes016.Stratum, Count(([WA Res 016 Final Selection].[Days])) AS CountOfDays
FROM WARes016 INNER JOIN [WA Res 016 Final Selection] ON WARes016.LRS = [WA Res 016 Final Selection].LRS
WHERE  [Days]<42
GROUP BY WARes016.Stratum HAVING
ORDER BY WARes016.Stratum;

[Killer42]

...
GROUP BY WARes016.Stratum HAVING
ORDER BY WARes016.Stratum;

HAVING what???

[NeoPa]

SQL is a fairly strong area of mine.
You probably don't want to use the DISTINCTROW predicate AS WELL AS the GROUP BY clause. In fact, unless I'm mistaken as to your requirements, the DISTINCTROW predicate will cause your query to give incorrect results under certain (rare) circumstances.
What you are missing is the WHERE clause. Try :

SELECT WARes016.Stratum, Count(([WA Res 016 Final Selection].[Days])) AS CountOfDays
FROM WARes016 INNER JOIN [WA Res 016 Final Selection] ON WARes016.LRS = [WA Res 016 Final Selection].LRS
WHERE [WA Res 016 Final Selection].[Days]<42
GROUP BY WARes016.Stratum
ORDER BY WARes016.Stratum;

[NeoPa]

Please let us know if this resolves your issues.

[ADezii]

HAVING what???

Thanks Killer, just left a 'Dangling HAVING'.

[KCangelstar]

NeoPa - that fixed it and it now counts correctly! Yay! Thank you and everyone else so much for your help!

[ADezii]

NeoPa - that fixed it and it now counts correctly! Yay! Thank you and everyone else so much for your help!

That NeoPa is something, isn't he?

[NeoPa]

NeoPa - that fixed it and it now counts correctly! Yay! Thank you and everyone else so much for your help!

That NeoPa is something, isn't he?

I'm pleased that worked for you - Thanks for letting us know :)
Thanks ADezii, coming from a respected peer that means a lot :)