Inner Join in Access

[keeda]

Hi

I have 2 tables say A and B.
Table A has EmpID, First Name and Last Name.
Table has Award Recipient, Award Presenter ID(EmpID format).

I want to write a select query where I want to fetch First and Last name of the Award Presenter from table A based on the Award Presenter ID from table B.

I believe I have to write inner join for this, but I have never written before. please help!

[tmcpolin]

Hi Keeda

Are you using Microsoft Access - if so what version?

If you are using Access then you are probably better of using lookup lists instead of writing the select query by hand. The reason for this is that access automatically writes the query for you by using the values you select whilst setting up the list.

Below is a step-by-step guide of how to create a lookup list:

1. 
   
2. Open table B in design view.
3. Select the Data Type column of the AwardPresenter field and click on the dropdown arrow.
4. Select Lookup List
5. Select ‘I want the lookup column to look up the values in a table or query’
6. Click OK
7. Select the table you want to look up (Table A)
8. Click OK
9. Select the fields you want to look up (Forename and Surname)
10. Click OK
11. Select any column you want data to be sorted by. You can leave this blank if you don’t require the data to be sorted.
12. Click OK
13. Adjust the width of the columns to whatever size you want
14. Click OK
15. Click Finish
16. A message box will then appear saying that you need to create a relationship. Click OK.
    

With the lookup list in place you will be able to select the name of the award presenter in the drop down list provided and the corresponding EmpID will be stored in the AwardPresenter field of table B.

For more information about drop down lists click here.

I hope this helps solve your problem. If you need any more help please let me know.

tmcpolin

[keeda]

Thanks tmcpolin!