Desperately need help

[jessenj]

I have two tables that I need to query data from both and match without a similar primary key.

Table1 (1 field named Username)
ajohnson
asmith
bsuydam
bsmthinson
cclarke
crobins
eerlich
edavis

Table2 (2 fields, firstname and lastname)
Anne Johnson
Catherine Clarke
Eric Erlich
Ed Davis
Alex Smith
Brian Smithson

What I need to do is do a match of each name in Table1 and find any records in Table2 that have a partial match.

Please help me if you can. I'm really desperate to get this done by close of business so I can go home for the holidays.

Thanks,

Jesse Rubin

 

[okiiyama]

Are you garaunteed that a user name is first letter of first name then last name?

You can do this:

select Table1.username, Table2.firstname, Table2.lastname
from Table1, Table2
where  mid(Table1.username, 1, 1) like mid(Table2.firstname, 1, 1)
and mid(Table1.username, 2, len(Table1.username)-1) like Table2.lastname

Hopefully this does the trick...
MID() gets a substring
--MID(COLUMNNAME, startPosition, NumberOfCharacters)

LEN() gets the length of a string
--LEN(COLUMNNAME)
----COLUMNNAME can also be any string.

Hope this helps.

 

[sfreeman]

Or, you can try this function with a query to give you the actual results [assumes that you have no typos - which you do in your sample data] and that the first table will always have first initial followed by last name:

Query:
'*********************************************
SELECT
Table1.Field1
, FindMatch([Field1])

AS
[Match]

FROM
Table1;
'*********************************************

Function:
'*********************************************
Public Function FindMatch(strInput) As String
Dim db As Database
Dim rst1 As DAO.Recordset

Dim intRet As Integer

Dim strSQL As String
Dim strVal1 As String
Dim strVal2 As String

Set db = CurrentDb

strSQL = "SELECT * FROM Table2"
Set rst1 = db.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

rst1.MoveFirst
With rst1
Do While Not .EOF
strVal1 = rst1.Fields(0)
strVal2 = Right(strInput, Len(strInput) - 1)
FindMatch = "No Match"
intRet = InStr(strVal1, strVal2)
If InStr(strVal1, strVal2) > 0 Then
FindMatch = strVal1
Exit Function
End If
.MoveNext
Loop
.Close
End With

End Function
'*********************************************
HTH - good luck!


Sam_F
"90% of the problem is asking the right question.