Thread: Matching correpondent IDs from first Table..

Hi all
I'm quite new in access and I have a qustion

Lets Say I have 2 tables in access db. First table presents info about aour colleagues, like below

Code:

ID   FirstName      SecondName   City             Phone
1     John               McArthur         New-York    5555555
2     Ben                Affleck            Boston         5555555
3     Will                Smith             Jersey          5555555
4    Silvestr           Stallone          Bombay       5555555
5     Lindsay          Lohan             Chicago        2222222

In second table, named as Purches I have many name (some of them are duplicate)

Code:

ID    PurchaseID    FirstName      SeconNAme      
1                          John               McArthur
2                          Lohan             Chicago        
3                          Silvestr           Stallone          
4                          Ben                Affleck            
5                          John               McArthur
6                          Silvestr           Stallone          
7                          Will                Smith 
8                          Lohan             Chicago        
9                          Will                Smith 
10                        Lohan             Chicago

As you can see Purchase ID Column is empty. So how I can bring correspondent ID numbers from first table.

In excel I could do that using INDEX and MATCH functions. I need smth like this here.

I have more than 500 records. Please help.

10 Lohan Chicago is OK.. It's not Lindsay Lohan ?

With only first and last name you could do something like this:

=INDEX($A$1:$A$100;MATCH(G2&H2;$B$1:$B$100&$C$1:$C$100;0))

comfirmed with ctrl+shift+enter

(change ; to , if you get error)