Query that seeks to match all records b/w two tables by either SocialSec # or Name

[Mike C]

Hello - I am new to Access, and I am trying to design a query that
will produce a list of all matches on Socials (one field in both
tables) or all matches on name (another bield in both tables) between
two tables.

If there is a match on socials, I would like that record included. If
there is a match on Names, I would like that record included. If there
is a match between both, I would like that record included only once.

Is there a simple way to set such a query up?

Thanks for any suggestions or step by step instructions.

 

[Arvin Meyer [MVP]]

Use a UNION ALL query.

Select * From Table 1
Where SSN = Forms!MyForm!txtSSN
UNION ALL
Select * From Table 1
Where LName = Forms!MyForm!txtLName

A word of caution, unless you are writting a payroll database, I suggest
that you NEVER store SSNs in your database. Access databases are only as
secure as your network and everyone that's allowed access to it. SQL-Server
has better built-in security, but you are still taking an unnecessary risk.

 

[Ken Sheridan]

You need to join the tables on an expression which uses a Boolean OR
operation. This will evaluate to True if either or both criteria are met,
e.g. with two tables Employees and Employees_1:

SELECT Employees.*, Employees_1.*
FROM Employees INNER JOIN Employees_1
ON Employees.SSN = Employees_1.SSN
OR Employees.EmployeeName = Employees_1.EmployeeName;

Two people can have the same name of course, which is why names should never
be used as keys, so you might get mismatches with the above.

BTW the join expression used above cannot be expressed in query design view,
so you'll need to write it and save it in SQL view. If you put the join
criteria in the WHERE clause however:

SELECT Employees.*, Employees_1.*
FROM Employees, Employees_1
WHERE Employees.SSN = Employees_1.SSN
OR Employees.EmployeeName = Employees_1.EmployeeName;

then it can be expressed in query design view by putting Employees_1.SSN in
the first criteria row of the Employees.SSN column and
Employees_1.EmployeeName in the second criteria row of the
Employees.EmployeeName column. In query design view criteria on separate
rows constitute an OR operation while those on the same row constitute an AND
operation. The latter requires both criteria to be met, rather than, as in
your case, only one at least.

Ken Sheridan
Stafford, England

 

[John W. Vinson]

Hello - I am new to Access, and I am trying to design a query that
will produce a list of all matches on Socials (one field in both
tables) or all matches on name (another bield in both tables) between
two tables.

If there is a match on socials, I would like that record included. If
there is a match on Names, I would like that record included. If there
is a match between both, I would like that record included only once.

Is there a simple way to set such a query up?

Thanks for any suggestions or step by step instructions.

I don't think you can use an OR in a JOIN clause... but you can use a
"Cartesian join" and do this in the WHERE clause:

SELECT <whatever>
FROM Table1, Table2
WHERE Table2.Social = Table1.Social
OR Table1.[Name] = Table2.[Name];

Note that if you in fact have a field named Name containing a full person's
name, you're making a couple of mistakes! Name is a reserved word and a bad
choice of fieldname; most people have two or three names (given name, middle
name, and surname) which should be in separate fields; names are NOT unique (I
once worked at a university where there was a Professor John Vinson; he got a
couple of my paychecks but alas I never got one of his); and you may get
mismatches if Table1 has "Robert Johnson" and Table2 has "Bob Johnson" for the
same person.

 

[Ken Sheridan]

John:

Before posting my reply to the OP I was dubious myself about whether an OR
operation could be used in a JOIN clause, so I copied a Contacts table,
changed some of the ContactID values in the copy table and then joined them
with an OR expression on the ContactID and LastName columns. It worked
fine, but I did of course get mismatches as both tables contained a number of
groups of people from the same families and consequently with the same last
names.

Ken Sheridan
Stafford, England

Hello - I am new to Access, and I am trying to design a query that
will produce a list of all matches on Socials (one field in both
tables) or all matches on name (another bield in both tables) between
two tables.

If there is a match on socials, I would like that record included. If
there is a match on Names, I would like that record included. If there
is a match between both, I would like that record included only once.

Is there a simple way to set such a query up?

Thanks for any suggestions or step by step instructions.

I don't think you can use an OR in a JOIN clause... but you can use a
"Cartesian join" and do this in the WHERE clause:

SELECT <whatever>
FROM Table1, Table2
WHERE Table2.Social = Table1.Social
OR Table1.[Name] = Table2.[Name];

Note that if you in fact have a field named Name containing a full person's
name, you're making a couple of mistakes! Name is a reserved word and a bad
choice of fieldname; most people have two or three names (given name, middle
name, and surname) which should be in separate fields; names are NOT unique (I
once worked at a university where there was a Professor John Vinson; he got a
couple of my paychecks but alas I never got one of his); and you may get
mismatches if Table1 has "Robert Johnson" and Table2 has "Bob Johnson" for the
same person.

 

[denzel]

Hello - I am new to Access, and I am trying to design a query that
will produce a list of all matches on Socials (one field in both
tables) or all matches on name (another bield in both tables) between
two tables.

If there is a match on socials, I would like that record included. If
there is a match on Names, I would like that record included. If there
is a match between both, I would like that record included only once.

Is there a simple way to set such a query up?

Thanks for any suggestions or step by step instructions.