Jan 26 2021 08:11 AM
This should be simple, but my SQL skills are rusty. I have a query that extracts the year and a count of incidents from a table.
SELECT year, Count(year) AS incidents
FROM Table2
WHERE ((Table2.cause_ID=15) OR (Table2.cause_ID=16))
GROUP BY year;
Of course, many years have no matching cause_ID = 15 or 16. I would like an output of year and 0 for those years.
Jan 26 2021 08:23 AM - edited Jan 26 2021 08:29 AM
If you want to see all years regardless of whether there are any matching cause_IDs for those years, then you can't use the WHERE clause to exclude them.
Instead, you need to use a conditional for the Count(year) to return the actual Count where there are matching cause_IDs or 0 where there are none.
SELECT year, Iif([cause_ID] = 15 or [cause_ID]=16), count(year), 0) AS incidents
FROM Table2
GROUP BY year;
Jan 26 2021 10:52 AM
wrong number of arguments used in query function lif([cause_ID) = 15 or [cause_ID]=16'.
Jan 26 2021 11:00 AM
@Clayton_cramerlif looks like a typo for if, but "undefined function 'if' in expression."
Jan 26 2021 11:07 AM
Iif() is the immediate If function; that's how it is spelled.
There must be a different syntax error in my suggested approach. It's aircode since I don't have visibility to your actual table(s). See if you can suss it out, but post back if not.
Jan 26 2021 11:08 AM
Wait, there may be a missing left paren. Add a second one after the first one
Jan 26 2021 11:39 AM
@George Hepworth I added that parenthesis and now I get "Your query does not include the specified expression 'lif([cause_ID]15 Or [cause_ID=16,count(year),0)' as part of an aggregate function.
SELECT year, Iif(([cause_ID] = 15 or [cause_ID]=16), count(year), 0) AS incidents
FROM Table2
GROUP BY year;
I would upload the database, but it is huge: every mass murder in American history from 1657 to 1914 (so far). year is a number, cause_ID is a number.
Jan 26 2021 12:47 PM
Reread what you entered, please. What you posted here is now missing a closing square bracket. It's also missing an equal sign. Trouble-shooting often requires taking a closer, second look at things.
Jan 26 2021 02:11 PM
I put everything in a format to simplify matching parens and brackets
SELECT year, iif (
(
(
[cause_ID] = 15
)
or
(
[cause_ID]=16
)
)
,
count(year), 0) -- matches opening ( of iif
AS incidents
FROM Table2
GROUP BY year;
All parens and brackets match. "Your query does not include the specified expression... as part of an aggregate function." I put the [cause_ID] clauses in their own parens to make sure iif was treating that as a single boolean operation. Even removing those parens, I get the same error message.
Jan 26 2021 02:54 PM - edited Jan 26 2021 03:59 PM
That's a different error. Let's try adding the expression to the GROUP BY.
Wrong approach, sorry. See the next post.
Jan 26 2021 03:57 PM - edited Jan 26 2021 04:51 PM
And my apologies. I didn't try it out before I sent it and I think this will actually give you the result.
SELECT year, Sum(IIF([cause_ID] = 15 or [cause_ID]=16) , 0,1) AS incidents
FROM Table2
GROUP BY year
That avoids the complication of having the Aggregate in either the GROUP BY or in the SELECT.
Jan 31 2021 07:22 AM
@George Hepworth"Wrong number of arguments used with function in query expression" followeed by the Sum(IIF expression. Shouldn't Sum only take one expression? It appears as if the IIF... 0,1 are being treated as three arguments to SUM.
Jan 31 2021 07:25 AM
I've always believed in the power of "trial and error" as a way to resolve such problems.
What happens when you change the suggested approach the way you describe?
Feb 01 2021 06:01 PM
IIF needs an expression and two values for TRUE and FALSE evaluations of the expression. It should return a single VALUE and SUM should add all those values for all rows. What you provided makes perfect sense--except to Access, which is still complaining about wrong number of arguments. It is almost like IIF does not recognize that expression.
Feb 01 2021 06:35 PM
@Clayton_cramer If you can, I would like some sample data to work with in order to see what might the problem is. Thanks.
BTW. Access most definitely will evaluate the expression once it is properly written, so please include your query with that expression with the sample data. Thanks.
Feb 02 2021 11:51 AM
I am embarrassed to say that I cannot figure out how to export my database with just a few records in it, so it is a size that I can upload.
Feb 02 2021 12:02 PM
@George HepworthI tried attaching the database:
Correct the highlighted errors and try again.
Feb 02 2021 12:04 PM
@Clayton_cramer If you can, compress it into a ZIP file and see if that will upload. If not, we can try an alternate method.
Feb 04 2021 02:10 AM
Solution@Clayton_cramer If you don't like IIF, then use IN:
SELECT
year,
Abs(Sum([cause_ID] In (15, 16))) AS incidents
FROM
Table2
GROUP BY
year;
Feb 04 2021 02:10 AM
Solution@Clayton_cramer If you don't like IIF, then use IN:
SELECT
year,
Abs(Sum([cause_ID] In (15, 16))) AS incidents
FROM
Table2
GROUP BY
year;