Query not retrieving all records

[datamerc]

Hi,

I'm joining two tables together in a Form as a Datasheet View, but I'm
stuck on a query that won't return all records. Here are my 2 tables:

customers (Table 1):
- customer_id (auto number, PK)
- first_name
- last_name
- etc...

gravesites (Table 2)
- gravesite_id (auto number, PK)
- grave
- block
- customer_id (from customers table)
- etc...

Basically all the records in the gravesites table are already filled
in, except the customer_id, which is blank, until the grave has been
occupied - then it will contain a customer_id. When I run a query to
combine these two tables in the Access Forms object so that users can
view all information (gravesites and corresponding customer info when
the gravesite is filled) from one screen.
The problem is that if a gravesite record doesn't have a customer_id in
it, the gravesite record won't be displayed. I'm sure this is because
the query joins the two tables based on the customer_id.

What do I need to do in order to get this query to show all the records
from the gravesites AND the corresponding customer records (if
applicable).

Thanks!

 

[Duane Hookom]

Double-click the join line, read the #2 and #3 options and select the
appropriate one. This creates a left or right join to return all the records
from your GraveSites.

 

[John Vinson]

What do I need to do in order to get this query to show all the records
from the gravesites AND the corresponding customer records (if
applicable).

I'd use a Form based on gravesites, with a Subform based on customers,
using CustomerID as the master/child link field.

If (for some reason) you really want to do this using a Query, select
the Join line by doubleclicking it and choose the option "Show all
records in gravesites and matching records in customers".

John W. Vinson[MVP]

 

[datamerc]

Thank you so much!