getting information from one field to two field

[basilyos]

hello guys

i have a table that contains Investigator1,Investigator2 and other fields

and another table that contains PID, Rank_Name_Family and other fields

am making a query in that query i want to get the names of Investigator1 and Investigator2

Investigator1 and Investigator2 is the PID

for example

Investigator1 = 1
Investigator2 = 2

PID = 1 Rank_Name_Family= Roy Jalbout
PID = 2 Rank_Name_Family= Rony Jalbout

i tried this two solutions but the result is error

Solution 1

Name1: DLookUp([Rank_Name_Family];"tbl_Personal_Information";[tbl_Personal_Information].[PID]=[tbl_Driving_Expert].[Investigator1])
Name2: DLookUp([Rank_Name_Family];"tbl_Personal_Information";[tbl_Personal_Information].[PID]=[tbl_Driving_Expert].[Investigator2])

Solution 2

Name1: DLookUp([Rank_Name_Family];"tbl_Personal_Information";[tbl_Personal_Information].[PID]=[Investigator1])
Name2: DLookUp([Rank_Name_Family];"tbl_Personal_Information";[tbl_Personal_Information].[PID]=[Investigator2])

any help??

 

[plog]

You don't normally use a Dlookup in a query, you bring in the data source, link it appropriately, and use it that way. You need to do this:

1. Bring in tbl_Personal_Information

2. Link it to Investigator1 field via PID

3. Change Name1 to this:
Name1: [tbl_Personal_Information].Rank_Name_Family

4. Delete Name2 from your query

5. Run it.

That will get you Name1 populated correctly. For Name2, you need to bring in another instance of tbl_Personal_Information, link it to Investigator2 field and use it to populate Name2 field.

 

[essaytee]

I agree with plog, no need for the DLookup function, configure queries accordingly.

I'm more curious as to your table design, seems like there should be a separate table solely for investigators involved in the incident and link to it. At present you are restricted to only two investigators per incident.

I maybe missing something as your example names are more or less the same but I am assuming they are in fact two separate investigators.

 

[basilyos]

plog thank you so much it works

essaytee thank you too they are not seperate but it's investigator and his assistant maybe i should names investigator - assistant

thanks again guys

before i join this access community i know nothing about access but now i know too many things

so many thanks to the owners and to the helpers