Access Question I am stuck on

[sai_rlaf]

No worries. I appreciate all of your help. and...it worked!!!



So a few questions, it looks like it went through each SDN list entry, compared it against every entry on my client list and returned any matches. And it did that for every SDN entry. Correct? I ask because it appears like some returned 50 of the same SDN list entry, so I should be able to run a remove duplicates to get a list of every entry that was a match right?

With that question out of the way, I have one more to ask, and I'm not sure if it's possible, but I will give it a shot.

This works for most entries, however, there may be a situation where the entry on the SDN list is Ramirez Herrara, where on the client list the last name may only be Ramirez. Using this query that would not be returned correct? Is there a way to get it to return if any word matches.

Using the example above, an SDN list entry Ramirez Herrara. Can I have it return on the query if a client has the last name Ramirez or the last name Herrara?

[TheShabz]

I've spent a LONG time trying to get something like that to work in my own situation and have not gotten any 100% successful method.

For example, I'll have a list of names. to compare, I have to try to match..
John Smith = John H Smith = John H. Smith = John Harrison Smith = John Smith Jr.. .etc

itll never happen.

As far as the duplicates go, I really cant say why it's happening without looking at the data. If there are duplicates in the tables, there will be duplicates in the results. Also, if you have, for example:
ID - FirstName - LastName - Address
1 - John - Smith - 123 anystreet
2. - John - Smith - 124 anystreet

you will get both, twice. It's somewhat of a cartesian product.

[sai_rlaf]

Duplicates is all sorted out. Not a big deal.

Is there a way to show all matches where it's a match to everything left of the space? I think that would get me cose enought to appease the boss.

[TheShabz]

Do you want to show "exists on SDN but not on clients" or "exists on clients but not on SDN"?

[sai_rlaf]

I'm not sure what your asking could you expand on that a bit?

[TheShabz]

I'm sorry, I misread. What do you mean by "left of the space?"

[sai_rlaf]

Okay so a common theme for Latin Americans or Arab Entries on this list is to have mulitple last names. However on our client list and in America it is typical to only record the first last name part.

So a common example would be an entry on the watch list having an entry Ramirez Herrera. On our client list however, we don't have an entry with a last name Ramirez Herrera, however, we do have a client with the last name Ramirez (the part left of the space). How do we get that the Ramirez on the client list to return even though it's not an exact match to the watch list data?

[TheShabz]

normally, you would use the Left() function in junction with the InStr() function to do this. However, in your case you would be doing it on the join itself. I've never tried to do that. I dont even know if it would work.

[sai_rlaf]

Let me run this by you and you tell me if this would work. I bring my list back out to excel. Run a text to columns so that each last name is contained in its own column. Bring that back to access.

Write that language we did previously, but do it for each last name column on my list (lastname1, lastname2, etc.). Then remove any duplicate entries that are returned on the query. Would that work?

[TheShabz]

It could work. However, you still risk false positives.

John, Smith James Wilson
would match
John, Smith Johnson Adams
as they are both technically John Smith. Which one is the real one?

[sai_rlaf]

Right, unfortunately though my firm is very risk averse when it comes to these things and they would rather have false positives than miss one. Speaking of which how would i write it if i wanted it to match two fields (last name and DOB).

Would it be

SELECT *
FROM SDNList INNER JOIN ClientList ON SDNList.LastName = ClientList.LastName AND SDNList.DOB = ClientList.DOB;

[TheShabz]

SELECT *
FROM SDNList INNER JOIN ClientList ON SDNList.LastName = ClientList.LastName
WHERE SDNList.DOB = ClientList.DOB;

Would work...

I would HATE to be named Muhammad and want to find a job at your place.

[sai_rlaf]

Don't worry. We don't stop at this point, we just have to manually go through the hits and see if they are actual matches are not. The problem with this particular list is, the data is very jumbled. If your interested in seeing what data I'm talking about, google "SDN List". Top link, scroll down for the CSV data source. Not common for the data of birth, id #, and job to be in one field. Only thing we can go on is "possible" name matches and then manually remove the rest.

[sai_rlaf]

How can I write to exclude null values?

[TheShabz]

in your WHERE statement include
AND FieldName Is Not Null