Query results show records w/diff License # when finds matching Last/First names

[jack R via AccessMonster.com]

What is best way to accomplish this -

I start with two Excel spreedsheets which I import into Access tables - Named

DIV - which has many records of persons not in the other TABLE - You can
think of this table as a DIVISION in the ARMY (Has a field named SOURCE and
all these records have DIV in this TABLE)

SLBOR - Where everyone in this table is also in DIV but there are many in DIV
that are not part of this - Like A battlaion in the army (SLBOR contains
names of persons in many different companies) - (Has a field named BOARD and
all these records have SLBOR in this TABLE)

I combined these tables into one table called COMBINED -- Is this right or is
this my first mistake.

What I need to do is find a Query (SQL Statement basically) that will
retrive/find records in the Combined Table that have:

Where it finds records with (EXAMPLE)
Records may be -
Last name First Name License ID SOURCE BOARD Address Company
Price, Jack, NM 123894,
SLBOR, 123 St, Jones Assoc
Price, Jack, 34521SA01, DIV,

The same Last name (Price) and First name (Jack) and DIV (In Source field)
and
The same Last name (Price) and First name (Jack) and SLBOR (In Board field),
but
the Field License ID do not match

And Will Not pull records that have the same license ID or where the records
with SOURCE DIV do not have a match in BOARD SLBOR Since there are many
persons in DIV orginal Spreedsheet that are not members of SLBOR

I am willing to restructure or what ever to make this work..


Please help... I can send part of the actual EXCEL Sheets, Access tables,
DBASE whatever to anyone who is willing to help with this.

 

[David Lloyd]

Jack:

A few suggestions. One, use the two base tables, DIV and SLBOR. Create an
INNER JOIN on the First and Last Name fields. You will then need to go to
SQL view (in the Query Designer) and add an UNEQUAL join condition for the
License ID. The FROM clause of the SQL statement might look something like
the following:

SELECT Field1, Field2, ...
FROM DIV INNER JOIN SLBOR ON DIV.FirstName=SLBOR.FirstName AND
DIV.LastName=SLBOR.LastName AND DIV.LicenseID <> SLBOR.LicenseID

Of course, with a join on first and last name, you may end up with many
non-matches on common names like John Smith, etc. If there are other fields
in the two tables that you can use to further tie the records from the two
tables together, I would recommend using them in the join.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


What is best way to accomplish this -

I start with two Excel spreedsheets which I import into Access tables -
Named

DIV - which has many records of persons not in the other TABLE - You can
think of this table as a DIVISION in the ARMY (Has a field named SOURCE and
all these records have DIV in this TABLE)

SLBOR - Where everyone in this table is also in DIV but there are many in
DIV
that are not part of this - Like A battlaion in the army (SLBOR contains
names of persons in many different companies) - (Has a field named BOARD and
all these records have SLBOR in this TABLE)

I combined these tables into one table called COMBINED -- Is this right or
is
this my first mistake.

What I need to do is find a Query (SQL Statement basically) that will
retrive/find records in the Combined Table that have:

Where it finds records with (EXAMPLE)
Records may be -
Last name First Name License ID SOURCE BOARD Address Company
Price, Jack, NM 123894,
SLBOR, 123 St, Jones Assoc
Price, Jack, 34521SA01, DIV,

The same Last name (Price) and First name (Jack) and DIV (In Source field)
and
The same Last name (Price) and First name (Jack) and SLBOR (In Board field),
but
the Field License ID do not match

And Will Not pull records that have the same license ID or where the records
with SOURCE DIV do not have a match in BOARD SLBOR Since there are many
persons in DIV orginal Spreedsheet that are not members of SLBOR

I am willing to restructure or what ever to make this work..


Please help... I can send part of the actual EXCEL Sheets, Access tables,
DBASE whatever to anyone who is willing to help with this.