Default value as lookup depending on another field

[isaacski]

Hi All!,
I have a tricky one... or hopefully perhaps very simple.

I have have a "master table" with Analyst ID and Analyst fields among other fields needed. Analyst ID is a number and Analyst will be the name of someone that corresponds to that number. I have a separate table that defines who is assigned to that particular ID.

1 Kim
2 Sarah
3 Beth

I have a form for this master table that shows Analyst ID and Analyst. I would like the default value of the Analyst field to be the name that corresponds to the Analyst ID number for the record. I would also like this same field to be a drop down on the form so that my users can change it as necessary. IE. If the record shows Analyst ID =1, the value for Analyst will show "Kim" unless changed to another analyst manually per the drop down.

This was rather difficult to explain so please let me know if you understand and if this is possible.. .I'm rather new to the concept of Lookup's in Access and I'm pretty sure this is what I will need.

Thanks in advance for the help!

Kim

 

[joeKra]

i need more information....
is Analyst ID the primary key for the record or the primary key for those 3 people?

 

[narang]

Hello,

The above process I believe can be achieved by creating a lookup field in the transaction table, where the Analystid is substitued for Analyst Name.

Rgds

 

[isaacski]

Hi guys,
I tried narang's solution and that did it. Instead of having two columns (one for ID and one for Name), I now have one column that records the numerical ID but shows the analyst name per the look up..

On the master table, the Analyst column has a numerical input, (1,2,3). I completed the lookup wizard using the link to the other table that shows whose name I would like to display for 1,2, and 3, etc. So now, the value of the record is 1 but it will display Kim, etc. Also, on the form when adding this field, it automatically makes the field a drop down where it displays the original number but can be changed if needed.

Thanks!