Sql recordset

[jerkyjerk]

Its an SQL question.
Im trying to embed SQL statement on my VBA.
I have the following tables

Table1 with Fields (Field1, Field2, Field3, Field4)
and
Table2 with Fields (Field1, Field2, Field5, Field6)
Their common fields are Field1 and Field2

I want to join these two tables based from the WHERE conditions of Table1
But whenever I tried to use INNER JOIN, they get different record counts.

This is the correct record counts
RECORD COUNT is 68:

SELECT COUNT(*) FROM TABLE1 A
 WHERE A.Field1='2015/07/15' And A.Field2>'9999999'

When I try to use INNER JOIN
RECORD COUNT is only 4
Note: the record count 4 are the 4 records on TABLE2

SELECT COUNT(*) FROM TABLE1 A
[COLOR=#ff0000]  INNER JOIN TABLE2 B
  ON A.Field1=B.Field1 AND A.Field2=B.Field2[/COLOR]
 WHERE A.Field1='2015/07/15' And A.Field2>'9999999'

What seems to be the problem.
I need to get the 68 records from TABLE1 with the added fields from TABLE2
Please help..

 

[Micron]

The inner join will only return records where the values are in both tables with respect to the fields that have been joined - this is the way it's supposed to work. Without knowing more about the data and relationships between the table fields, we can only guess. Try an outer join (I presume left outer join). If neither left or right outer joins work, you will probably have to create a UNION query.

 

[jerkyjerk]

^^ I tried LEFT JOIN and it works!
Thanks

 

[Micron]

Glad to help! There are more query "tricks" you may have to learn to get what you want in the future. Like 'unique records' and 'unique values' for instance.

 

[jerkyjerk]

Glad to help! There are more query "tricks" you may have to learn to get what you want in the future. Like 'unique records' and 'unique values' for instance.

Thanks
will study more of that