Dahlman said:
I'm not sure what outerjoins are. I just entered "N/A" in the fields
I found were necessary but if there is a better way and explaining
outerjoins isn't too difficult then that would be cool. Thanks again.
This explanation is going to be rather simplistic and not fully
accurate, but I hope it will tell you what you need to know.
A inner join (the default type of join) between two tables matches up
the records in the tables based on the key fields they have in common,
and includes in the resulting set of records only those records where a
match is found. You can select fields from both tables to appear in the
query's result set, and they will contain values as you'd expect, from
the matched records. Usually you'll have one record in one table
matching several records in another table, and the field values from the
"one" table will be repeated for every matching record in the "many"
table. But if there is no match for a record, whichever side of the
join it's on, that record won't be included in the result set at all.
Obviously, if the matching key field is Null, there's no way for that
record to match anything, so that record will be dropped from the
results.
An outer join does the same match, but it includes not only the records
that have matching keys, but also the records that *don't* match, from
one of the tables or the other. (Note: some database systems support a
"full outer join" that includes the unmatched records from both tables,
but Access does not -- you get one side or the other, but not both.) So
the result set contains *all* the records from one of the tables, but
only data from the matching records in the other table. Records in the
result set that have no matches have Null values for the fields that
would otherwise come from the matching record.
An outer join can be either a "left" join or a "right" join, depending
on which of the pair of tables being joined is the one you want all the
records from. "TableA LEFT JOIN TableB" returns all the records from
TableA, but only the matching records from TableB, whereas "TableA RIGHT
JOIN TableB" returns all the records from TableB, but only the matching
records from TableA.
This is how you get around the problem of making a report on records
that may or may not have matches in the lookup tables. You use an outer
join, left or right as appropriate, to make sure that all of the records
you want to report are included in the query results. Sure, the lookup
values will be Null, but the records will be there.
You can make a left or right join in the query design view by
double-clicking the join line between the tables and choosing the second
or third option in the Join Properties dialog. Note that it's a bit
tricky to bring up this dialog -- you have to double-click right on the
join line, or else all you'll get is the query's property sheet.
