sql including row even if values are null MS-Access

Question

I'm creating a query that gets the amount of retired people per fiscal year. Let's say I have this table:

 table1

|  FY   |  Reason for leave |
|2011-12|       sick        |
|2011-12|       retired     |
|2011-12|       retire      |
|2012-13|       sick        |
|2014-15|       retired     |
|2014-15|       retired     |
|2014-15|       retired     |

My Query is:

Select FY, Count(*) FROM table1 WHERE [Reason for leave] = "retired";

The problem is it won't return 2012-13 as one of the fiscal years, which makes sense, but I'd like for it to include all the years with either 0 or a blank as the count column

Thanks alot in advance!

Side note: I recommend you change your column type for FY to either int, char(4), or Date. Basically store the fiscal year, not the year range which adds no benefit. Using int or date (set to month = 1, day = 1) will make it possible to constrain values, filter values, and also perform calculations. If you really must then use char(4). What you have in there now makes it possible for an application error (or manual update) to enter an invalid value like 2015-14 and you won't know until you discover that a query result does not return what you were expecting. — Igor
– Igor, Commented Jun 8, 2017 at 18:43

Lord Peter · Accepted Answer · 2017-06-08 18:38:46Z

1

If you have a separate table of years you could left join to your table1. If you don't, you could use something like this:-

select FY, sum (iif([Reason for leave]="retired",1,0)) as Count_Retired
from table1
group by FY;

answered Jun 8, 2017 at 18:38

Lord Peter

3,5012 gold badges36 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tee Over a year ago

Thanks alot I can't believe I didnt think of that!

Collectives™ on Stack Overflow

sql including row even if values are null MS-Access

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related