SeRene said:
Hi Dirk,
I am not really very good in Access.
Can you enlighten me on the inner join part?
Suppose you have two tables, TableA and TableB, with these fields:
table: TableA
field: A_ID (primary key)
field: A_ShouldMatch (your matching field)
field: A_Stuff (some other data)
table: TableB
field: B_ID (primary key)
field: B_ShouldMatch (your matching field)
field: B_Stuff (some other data)
And suppose you want to find all records in TableA for which
A_ShouldMatch has a match in the B_ShouldMatch field in TableB. You can
create a new query in Design View and add both TableA and TableB to the
query. If any join line appears automatically between the tables, click
on that line and delete it. Then drag the field A_ShouldMatch from
TableA and drop it on field B_ShouldMatch in TableB. That should create
a line joining the two fields. If the line has an arrow on either end,
then right-click the join line, click "Join Properties" in the popup
menu, and in the Join Properties dialog choose option 1, "Only include
rows where the joined fields from both tables are equal." This is an
inner join. Then click OK to close the dialog.
Now drag the fields you want, or the "*", from TableA and drop them on
the field grid. If there are any fields from TableB that you'd like to
see in your query, drag them down, too; otherwise there's no need to
drag anything from TableB. If you switch the query to datasheet view,
you'll now see every record in TableA that had a match in TableB.
There's only one possible hitch: if TableB has multiple records with
the same value in B_ShouldMatch, then each matching record in TableA
will be repeated multiple times in the query results, once for each
matching TableB record. If this is the case and you don't want to see
these duplicates, you can set the query's "Unique Values" property to
Yes. This is on its property sheet in design view. Be aware, though,
that this will make the query's result set "nonupdatable". If this is a
problem for you, post back and we'll come up with an alternate solution.
