MS Access, How can I compare two dissimilar columns and create a report?

[TheBluePill]

MS Access, How can I compare two dissimilar columns and create a report?

Hello Folks,

I have a quick MS Access Question..

I have two Tables that contain people information from different sources.

Table 1 has independent Columns for First Name, Middle Name, Last Name, ETC

Table 2 has a Colum with the Whole Name in one field (First, Last).

I need to take Table 1, with the First, Middle and Last Name and Check to see if those names match any of the names on Table 2 and generate a report.

How can I do this?

Thanks
T

 

[hooflung]

VBA code. You will have to write out some visual basic to do this.

 

[TheBluePill]

Originally posted by: hooflung
VBA code. You will have to write out some visual basic to do this.

Any Starting Point or Code I could Borrow?

 

[MrChad]

SELECT * FROM Table1 WHERE EXISTS (SELECT 1 FROM Table2 WHERE WholeName = (Table1.FirstName & ", " & Table1.LastName))

This *might* work. I don't have Access available to test it out.

 

[Crusty]

There's no need for any VBA here. I'm fairly certain you can use substrings or string splits in queries. I just haven't touched Access syntax in ages so I forget

 

[mcmilljb]

Originally posted by: MrChad
SELECT * FROM Table1 WHERE EXISTS (SELECT 1 FROM Table2 WHERE WholeName = (Table1.FirstName & ", " & Table1.LastName))

This *might* work. I don't have Access available to test it out.

I tried this out in Access:

SELECT wholeName
FROM Table2, Table1
WHERE Table2.wholeName=(Table1.firstName & ", " & Table1.lastName) OR Table2.wholeName=(Table1.middleName & ", " & Table1.lastName);

It worked for simple data sets, but this is using Access 2000.

 

[TheBluePill]

Thank You All for the great starting points!!!

I have been playing with it all weekend using your advice and I have built the following Query;


SELECT DISTINCT wholeName
FROM Table2, Table1
WHERE wholeName Like ("*" & Table1.lastName & "*") AND wholeName Like ("*" & Table1.firstName & "*");


Now..I've hit another stumbling block;

What If I have a Table3 that also has a wholeName column and I want to include it in my search against the items in Table1?

Thanks!!

 

[KB]

If your query was working then you should just be able to add the new table to the query with just an additional condition

SELECT DISTINCT Table1.LastName & ", " & Table1.FirstName As wholename
FROM Table2, Table1, Table3
WHERE (Table2.wholeName Like ("*" & Table1.lastName & "*") AND Table2.wholeName Like ("*" & Table1.firstName & "*")) OR (Table3.wholeName Like ("*" & Table1.lastName & "*") AND Table3.wholeName Like ("*" & Table1.firstName & "*"));