Solved Null field not return (1 Viewer)

[smtazulislam]

Hello, any help much appreciate..

SELECT tblEmployeeSalaries.SalaryID, tblEmployeeSalaries.EmployeeID, tblEmployee.EmployeeName, tblEmployee.DeptID, tblEmployee.CountryID, tblEmployeeSalaries.DateFrom, tblEmployeeSalaries.Basic, tblEmployeeSalaries.Food, tblEmployeeSalaries.Others, tblEmployeeSalaries.Increase, tblEmployeeSalaries.sGrossSalary, tblEmployeeSalaries.Note, LtblAllowaceType.AllowanceType, tblAllowanceData.TotalAllowance, tblAllowanceData.AllowanceTypeID
FROM (tblEmployee INNER JOIN (tblAllowanceData INNER JOIN LtblAllowaceType ON tblAllowanceData.AllowanceTypeID = LtblAllowaceType.AllowanceTypeID) ON tblEmployee.EmployeeID = tblAllowanceData.EmployeeID) INNER JOIN tblEmployeeSalaries ON tblEmployee.EmployeeID = tblEmployeeSalaries.EmployeeID;

I try to this column null filed display in the report. for that I try to create a query and put critaria "IsNull" and also "Is Not Null" or "Not Is Null"

tblAllowanceData.TotalAllowance

But result dont display null field record.
its always display entry fields...
Any idea !

 

[June7]

You only want records where TotalAllowance is Null? Possibly need to use LEFT or RIGHT JOIN.

 

[The_Doc_Man]

I'm with June7 here. I'll amplify. When you do an INNER JOIN you are telling Access/SQL that you want records from two tables where the matching fields are not null. I.e. to match, they must exactly match and the problem with nulls is that nothing will EVER match them. But your question is slightly confusing since there are two ways to interpret it.

a. Matching records for both tables on values being equal (and necessarily not null) - which would be an INNER JOIN case, and the nulls you are talking about are not any of the JOIN fields. You are using INNER JOIN in the query you showed us, so if the match criteria included null cases, they won't be present.

b. One of the tables is independent and the other is dependent. Using a LEFT JOIN (with independent table on the LEFT and dependent table on the RIGHT will give you all the matching records but will ALSO give you records from the left-side table even if unmatched, and any fields normally derived from the right-side table will be returned as nulls for the unmatched records. In that case, an ISNULL or NOT ISNULL test would make sense.

 

[smtazulislam]

You only want records where TotalAllowance is Null? Possibly need to use LEFT or RIGHT JOIN.

Thank you so much for your reply...
if its was two table. But there is 4 tables JOIN. I can do it.
I give a demo for two tables:

FROM tblEmployeeSalaries LEFT JOIN tblAllowanceData  ON tblEmployeeSalaries.EmployeeID = tblAllowanceData.EmployeeID;

First table is 1) tblEmployee > 2) tblEmployeeSalaries And tblAllowanceData > 3) LtblAllowaceType.
OR
1) tblEmployee > 2) tblEmployeeSalaries > 3) tblAllowanceData >4) LtblAllowaceType.
This four tables I don't understand how to JOIN LEFT/RIGHT with EmployeeID added.

 

[arnelgp]

on query design right-click on each Lines connecting to tblEmployeeSalaries table.
on the Join Properties, select "include ALL records from tblEmployeeSalraries and only those records from...".

 

[smtazulislam]

I'm with June7 here. I'll amplify. When you do an INNER JOIN you are telling Access/SQL that you want records from two tables where the matching fields are not null. I.e. to match, they must exactly match and the problem with nulls is that nothing will EVER match them. But your question is slightly confusing since there are two ways to interpret it.

a. Matching records for both tables on values being equal (and necessarily not null) - which would be an INNER JOIN case, and the nulls you are talking about are not any of the JOIN fields. You are using INNER JOIN in the query you showed us, so if the match criteria included null cases, they won't be present.

b. One of the tables is independent and the other is dependent. Using a LEFT JOIN (with independent table on the LEFT and dependent table on the RIGHT will give you all the matching records but will ALSO give you records from the left-side table even if unmatched, and any fields normally derived from the right-side table will be returned as nulls for the unmatched records. In that case, an ISNULL or NOT ISNULL test would make sense.

Yes Sir, I also agree with her.
But problem is two is main and two is subMain. So I dont understand how to Add LEFT or RIGHT.
How I JOIN (ID).... Many way myself I try... but result is ZERO...

 

[smtazulislam]

on query design right-click on each Lines connecting to tblEmployeeSalaries table.
on the Join Properties, select "include ALL records from tblEmployeeSalraries and only those records from...".

Appreciate Mr. @arnelgp . Your are genius. Maybe I get my answer.
I try and give you feedback...

 

[smtazulislam]

Hello Mr. @arnelgp . Its work . And this query have now display 3 data records.
can I display as attached picture as A QUERY 5 data records. Is it possible... I know there is no EmployeeID data record, Any ways putting "0" and display in the query. I am not sure can do it ...

???????

 

[mike60smart]

Select Each Join between tblEmployees and tblEmployeesSalaries, change the Option from 1 to 2
The arrowhead will then point to tblEmployeesSalaries

Do this for the other tables.

If Option 2 does not work then select Option 3

 

[arnelgp]

yes you can.
drag the other 2 tables to your query design.
join each table to tblEmployee (join by EmployeeID).
and use the technique in post #5.

 

[smtazulislam]

Select Each Join between tblEmployees and tblEmployeesSalaries, change the Option from 1 to 2
The arrowhead will then point to tblEmployeesSalaries

Do this for the other tables.

If Option 2 does not work then select Option 3

If chosen option 2 But there only display 3 Data.
and Option 3 its not working.

 

[smtazulislam]

yes you can.
drag the other 2 tables to your query design.
join each table to tblEmployee (join by EmployeeID).
and use the technique in post #5.

Some edit,
I am sorry, some mistake.
I want display 4 data records.

 

[smtazulislam]

I can print out 1-3 employeeID data as well.
When I try print out employeeID - 4 & 5 then get error.
Upload sample copy...

 

[mike60smart]

I can print out 1-3 employeeID data as well.
When I try print out employeeID - 4 & 5 then get error.
Upload sample copy...

Query now displays 4 records

 

[smtazulislam]

Query now displays 4 records

Thank you so much, appreciated.
Yes, this is I looking for my query... And also I find my mistake.

 

[mike60smart]

We were glad to help