My first access database - new foreign key record

[michal_milena]

Hello,

I'm new here also I just stared to learn Access 2007, and unfortunatelly I have a silly problem. On study I learned SQL and somethimes I think that there everything seems to work a bit diffrent, I forgot many things and I meet with trivial problem. I know that for somebody who know the topic I will be a quicky and simply case, but I stuck here and can't resolve it. I projected and builded my whole first database (all tables), but when I wanted to test it and fill up I meet with my problem. I gonna cut my whole database to only 2 tables becouse there the is the whole thing. I have tables:

Type_of_position:

-idType_of_position (PRIMARY KEY)


-Name_of_position (VARCHAR (255))
-Comments (VARCHAR (255))

Employee:

- idEmployee (PRIMARY KEY)
- First_name (VARCHAR (255))
- Last_name (VARCHAR (255))
- Locker_number (INT)
- Position (VARCHAR (255)) ------- here I have a problem

When I create a new record (fill up a new worker) I want to choose a name of position from available, and put it to this field. I tried many times and combinations, and the only thing I meet was that idType_of_position (Type of position Table) need to be the same as in Employee table (idEmployee) but of course it's not this what I want to be. Do I need to create a one more field in Employee table for example position_ref and then create a relationship beetwen this field and idType_of_position? I know only how to set in design view type of field to show a list of available values, but how to do that when I create a new employee I can choose one of the position type, and store name of this position in "position" field?. Of course I don't mean to combined as I did before (one ID from type of position to one ID of employee) becouse of course many employees can have this same position name. I suposse that everbody will know what I want, but I always writes to many yey, I would be really gratefuly for any advice, or help becouse I don't know how to do it.

P.S. sorry for my language but I just still learn English

[June7]

Why save position name in Employees table - save the position type ID as it is the primary key in Position table. Why are Type_of_position_idType_of_position and Employee_sizes_idEmployees_sizes indicated as primary key fields?