Finding matching records

  • Thread starter Thread starter SeRene
  • Start date Start date
S

SeRene

Hi, I notice that Access Queries allow me to find
unmatched records.
However, i need to find matching fields. Not unmatched
records.

I need to match a field in one table against another field
in another table.
Is there any way i can do this??

I need help!!
Thanks lotZzzzZZz
 
SeRene said:
Hi, I notice that Access Queries allow me to find
unmatched records.
However, i need to find matching fields. Not unmatched
records.

I need to match a field in one table against another field
in another table.
Is there any way i can do this??

I need help!!
Thanks lotZzzzZZz
Click to expand...

An inner join in a query will return only records where the join
field(s) are equal. Note that it won't return records where the join
fields are Null, because by definition, Null <> Null.
 
-----Original Message-----


An inner join in a query will return only records where the join
field(s) are equal. Note that it won't return records where the join
fields are Null, because by definition, Null <> Null.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Hi Dirk,
I am not really very good in Access.
Can you enlighten me on the inner join part?
 
SeRene said:
Hi Dirk,
I am not really very good in Access.
Can you enlighten me on the inner join part?
Click to expand...

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