Count blank fields from a LEFT JOIN query

[Kill_Switch]

Hello AWF, been a while, but still been lurking.

I've been down so many combinations that I've lost where and what I did.

I have a master list of employee positions. Not all positions are filled by the employees. I wish to count the number of blank positions where an employee is not in to.

The Left Join query pulls correctly, I just want to count the number of blank HRMS (not filled positions). I'll later expand the query to blank positions by their respective trade, mechanic, electrician, etc.

Basically I was a percentage of filled positions. Caveman math below of 2 counts I'm trying to achieve.

Sum = count #of blanks in [HRMS]/sum of [POSITION] *100
Sum = count # of blanks in [HRMS] where [TRADE]="Electrical"/count of [POSITION], where [TRADE]="electrical"



I have 2 tables (relationship below)
- tbl_AER
- tbl_MBR_MASTER

SELECT tbl_AER.POSITION, tbl_MBR_MASTER.HRMS, tbl_AER.JOB_TITLE, , tbl_AER.TRADE
FROM tbl_AER
LEFT JOIN tbl_MBR_MASTER ON tbl_AER.POSITION = tbl_MBR_MASTER.HRMS;

Kindest of Regards,

 

[Ranman256]

=DCount("*", "qsMyQuery", "[HRMS] is null")

 

[Kill_Switch]

=DCount("*", "qsMyQuery", "[HRMS] is null")

That worked perfectly!
Thank you very much. I'll be adding this to my quick referance guide!

 

[plog]

Here's an SQL solution:

Take your existing query and let's call it 'sub1'. Then use the below SQL to get the total blank position ratio:

SELECT Sum(IIf(IsNull([HRMS]),1,0))/Count([POSITION]) AS BlankFactor
FROM sub1;

Save that query as 'Main'. To get the ratio for just 'Electrical', you add that criteria into 'sub1', save it and then just run 'Main' again.