Query in report not showing all records

[colemauro]

Hi there,

Im having a problem, and to be honest im complety lost to reason for this, so here I am.


I have this four way query that has the following:


A – Hardware table were i pick up some values
B – User table were I pick up username
C – HW/User table were I match the hardware to the user that uses it
D – Another table also associated with HW were I pick up a security class value

Inside the HW table, I have a boolean field (type Yes/No), were I say if the hw is standard or not.


The problem is with a report I created for this. From a total of 117 records, i have 7 where that boolean field is empty. The problem is that it just will not show 3 of those 7 records. Even if i take that boolean field from the query, the result is the same, it will show 114 records.


The relationship is ok, there is no filter anywhere so I am lost.

SELECT tb_hw.hw, tb_hw.hw_type, tb_hw.hw_modelo, tb_hw.hw_serial_number, tb_hw.hw_standard, tb_colaborador.nome, tb_hw_scs.avail
  FROM (tb_hw INNER JOIN (tb_colaborador INNER JOIN tb_hw_colaborador ON tb_colaborador.id = tb_hw_colaborador.id_colaborador) ON tb_hw.id_hw = tb_hw_colaborador.id_hw) INNER JOIN tb_hw_scs ON tb_hw.id_hw = tb_hw_scs.id_hw
  WHERE (((tb_hw.hw_standard)=false))
  ORDER BY tb_hw.hw_type;

Can someone help me?


Best regards


Mauro Fernandes

 

[vbaInet]

3 of those 7 records do not exist in the parent table. Change your join from INNER JOIN to LEFT JOIN or RIGHT JOIN, depending on which one has more records.

 

[colemauro]

It solved the problem. Thank you very much.

But im having problems understanding the reason for the problem i had.

Will try to think on what you said.

Thanks again.

 

[vbaInet]

Think about it like this. I have two tables, Table A and Table B. Table A is the parent table and I join Table A to Table B via some ID field. Now, imagine that we have the following records:

Table A
1
2
3
4
5

Table B
1
2
3

After joining the result will be

Table A and Table B
1
2
3

But if I do a LEFT JOIN from Table A to Table B, i.e. Table A -> Table B, then I will get:

Table A -> Table B
1
2
3
4
5

... for the ID field of table A.

 

[colemauro]

Think about it like this. I have two tables, Table A and Table B. Table A is the parent table and I join Table A to Table B via some ID field. Now, imagine that we have the following records:

Table A
1
2
3
4
5

Table B
1
2
3

After joining the result will be

Table A and Table B
1
2
3

But if I do a LEFT JOIN from Table A to Table B, i.e. Table A -> Table B, then I will get:

Table A -> Table B
1
2
3
4
5

... for the ID field of table A.

I understand now.

Thanks

 

[vbaInet]

No problem!