Query not retrieving all records

  • Thread starter Thread starter datamerc
  • Start date Start date
D

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!
 
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.
 
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).
Click to expand...

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]
 
Back
Top