retrieving incomplete records on a form

[Jon M.]

I have a form based on two tables which are linked by a record#. One table
is simple employee info first name, last name, id#, etc... the second table
is a bunch of checkboxes for different things relating to the employee. My
issue is that if I enter only partial info in a new record on my form, for
example I put in only Last Name, and nothing else on the record and close my
form, when I reopen my form I cannot find that record using a "Find Record"
command button searching the last name field even though there is an existing
record on my first table. Am I missing something here? If there's a record
in one table, shouldn't my form be able to find it? As always any help is
greatly appreciated!

 

[Steve Sanford]

Hi Jon,

It sounds like your form is based on a query. If you look at the SQL of the
query, you will find the two tables are joined by an inner join. I think of
this as an equi-join.
It means the query will only return the records where the linking fields
have the same value.

In your case, when you enter only a last name, a record in the second table
is not created. Thus, the partial record is not displayed in the form.

To show partial records from the "1" table, you need to use a Left Join.

So do this:

In query design view, click on the line between the tables and select
properties. Arrange the tables so the 1 tabel is on the left and the many
table is on the right. (Just so we are looking at the same arrangement).

Then select option 2. Click OK. The joining line should now have an arrow
pointing to the many table (pointing to the right).

Save the query.

You should be able to to searches for any names, even if they do not have a
matching record in the "many" table.

HTH