Querying Data that is not in each table

[jeff3457]

Hi,

I have built a query from 4 different tables. Simply, I want to maintain the data from the first table and add in data if present from the other 3 tables.

My first table has all employee numbers, I am using that to link to the other 3 tables. My issue is not every employee shows in the other 3 tables. I believe that is causing my query to drop that employee. Is there a way to fix this? I would like my query to still show every employee and just provide a 0 or no value if they are not present in one of the tables.

 

[Isaac]

Look into Left Joins rather than Inner Joins.

 

[jeff3457]

Look into Left Joins rather than Inner Joins.

Thank you! That worked and was much simpler than I anticipated.

Is there a way to have it show any absent data as zero? It isn't needed but would be nice to add.

 

[Isaac]

Great! Glad it helped.

You can create a new column like this:

DataIsAbsent: iif(isnull([othertable].[fieldyoujoinedon]),0,1)

Which would put a 0 if the other table didn't have it
edited the 1 vs. 0 reversal

 

[arnelgp]

simply use:

Absent: Nz([TableName.[FieldName], 0)