don't understand outer join

[Guest]

I just need a relationship for a value list. I want all values for a field
called subject matter in a table to be related to a table called cases
between two fields called subject matter.
Is this an outer join in the properties. I don't think it is an equi-join
or cartesian product?
Just checking to be sure. Thanks,

 

[Ken Snell \(MVP\)]

Depends upon which data records you want returned. If just those that have
matches between the two tables, you use an INNER JOIN. If you want data
records regarless of whether there are matches in the other table, you use
an OUTER JOIN (either a LEFT JOIN or a RIGHT JOIN, depending upon which
table is the one from which all records are to be returned).

 

['69 Camaro]

Hi, Janis.

I want all values for a field
called subject matter in a table to be related to a table called cases
between two fields called subject matter.

Your terminology is confusing to me. If I understand you correctly, you want to
match up a single column in one table to two columns in another table.

Is this an outer join in the properties.

Perhaps the following explanation will make it easier to visualize the
relationship between the two tables. Envision a table on the left and a table
on the right.

1.) INNER JOIN: list all records where the values match in both tables on the
join columns.
2.) LEFT OUTER JOIN: list all records in the table on the left. Include
records on the right if and only if there are matching values in both tables on
the join columns.
3.) RIGHT OUTER JOIN: list all records in the table on the right. Include
records on the left if and only if there are matching values in both tables on
the join columns.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[John Vinson]

I just need a relationship for a value list. I want all values for a field
called subject matter in a table to be related to a table called cases
between two fields called subject matter.
Is this an outer join in the properties. I don't think it is an equi-join
or cartesian product?
Just checking to be sure. Thanks,

Don't confuse a RELATIONSHIP - a permanent constraint on two Tables -
with a QUERY - a dynamic object joining two tables, which might use
the same join type as the default relationship and might not.

If you want to see all records in the SubjectMatter table, with those
records in the Cases table where there happens to be a match (and
NULLS in the Cases table fields where there is no match), then you
need to create a Query with a Left Outer Join. If you have set the
relationship between those two tables with the Join Type defined as a
left outer join, you'll get it by default; if you haven't, no problem,
just create the query with the default inner join, select the join
line, and choose the option that gives you what you want (Option 2 or
3, whichever says "show all records in SubjectMatter and matching
records in Cases").

John W. Vinson[MVP]