>This is where you will wantto use your inner and outer joins (in SQL syntax). To do so through the designer window try the following to see how it works:
>
>Table 1: Staff
>Column1: StaffID - Autonumber
>Column2: StaffName - Text
>(populate Table 1 with random data)
>
>Table 2: StaffAllergies
>Column1: StaffID - Number
>Column2: AllergyType - Text
>(populate Table 2 with StaffIDs corresponding to StaffID in table Staff and random text for AllergyType so that for every StaffID in Staff you have at least one record with the same StaffID in StaffAllergies)
>
>Open up a new query and add Staff and StaffAllergies. Use your mouse and click and hold on StaffID in Staff. Then drag to StaffID in StaffAllergies. You should now have a Join represented by an line. Add fields StaffID from Staff and AllergyType from StaffAllergies. Execute the query and check your results.
>
>Save your query. Now go back to your Table view and add a new entry to Staff but DON'T add a corresponding entry to StaffAllergies. Go back to your query and execute it again. You will see the new entry you put in to Staff does not appear since there is nothing to join to in StaffAllergies.
>
>Now go back to designer view. Right-click on the line that represents your Join and select Join Properties. You will see a window pop-up with three choices. Have a read through of the choices. Choose item 2 (Select ALL records from 'Staff' and only those records from 'StaffAllergies' where the joined fields are equal. Click OK. execute your query again. You should see in the results the new StaffID with an empty AllergyType value (since no join exists). Hope this helps explain it a bit.

Thanks, Jason and sorry for additional thread. For me with a VFP background is much easier to right SQL just in SQL form without using designer. Also both my tables have empty date field. In VFP this select statement would not filter empty records, e.g.
select * from table1 inner join table2 on table1.field1=table2.field1 and table1.field2=table2.field2 would select every record including empty field2 in both tables records. Why it's different in Access?

I've tried right join, but it didn't give me the result, I want. Left join doesn't work either...

Looks like this simple thing could not be done in Access at all :(