Easy Query Question

[DM - NPS]

I am trying to create a query that shows all the records in a table called
Species (425 records). The table has 20 fields. I want the query to contain
3 fields (ID, Name, and Species). The Species field is a code that links to
another table called Name that contains the full name of the species.

When I run the query I get 402 records. This is because 23 of the records
do not have a species code populated. How do I setup my query to show all
records even if a linked field has a null value?

Thanks in Advance

 

[Dennis]

Right click on the join line in the query design grid and then select edit
join properties. Click the option to show all records in your main table and
only those in the joining table where the fields match.

 

[Bob Barrows [MVP]]

I am trying to create a query that shows all the records in a table
called Species (425 records). The table has 20 fields. I want the
query to contain 3 fields (ID, Name, and Species). The Species field
is a code that links to another table called Name that contains the
full name of the species.

When I run the query I get 402 records. This is because 23 of the
records do not have a species code populated. How do I setup my
query to show all records even if a linked field has a null value?

Use an outer join. If you right-click the line between the two tables in
the Query Builder and select Join Properties, you will see a dialog in
which you can specify the type of join. You want to specify the on that
returns all the records from Species but only the matching ones from
Name.

You might want to consider using more descriptive field and table names.
I have no idea what the ID field is, so "ID" is probably a bad name. The
field called "Species" is probably better named "SpeciesID". The word
"Name" is reserved in most databases and should probably be avoided. So
the Name table should probably be called SpeciesNames. And the Name
field (which is in SpeciesNames, right?) should be called SpeciesName.