# Count blank fields from a LEFT JOIN query (1 Viewer)

#### Kill_Switch

##### Go Easy I'm New
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

Code:
``````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,

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

=DCount("*", "qsMyQuery", "[HRMS] is null")
That worked perfectly!
Thank you very much. I'll be adding this to my quick referance guide!

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:

Code:
``````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.

Replies
3
Views
508
Replies
11
Views
449
Replies
7
Views
1,102
Replies
4
Views
548
Replies
15
Views
895