First occurrence

[Guest]

I have a master team list table which has two columns: PilotCode and
AgentName. The table has multiple instances of PilotCodes with the
AgentsName spelled differently e.g.

76AO Ana Ortiz
76AO Anna Ortiz
76AO Ann Ortiz
76AO Ana Ortiz

I have to create a link between two tables to pull out the AgentName .
PilotCode is the common field between two tables. How can I just include the
first occurrence of the AgentName based on the PilotCode. I used the
following Query but it captures all the values:

SELECT *
FROM TblLookup_Team_Agent_Pilot AS P1
WHERE (P1.PilotCode)=(SELECT MIN(PilotCode)
FROM TblLookup_Team_Agent_Pilot as P2
WHERE P2.PilotCode=P1.PilotCode);


Can someone help? Thanks a lot!

Purnima Sharma

 

[John Spencer]

Answered in your prior posting. 15 minutes between posting the same
question is a bit impatient. This forum is manned by volunteers and
responses can take a while (sometimes forever).

You did a good job of posting your question and your attempted solution. As
a consequence, it is easier to determine what you want and suggest possible
solutions.

 

[Guest]

Hi John,
It gave me an error message that the system was down when I posted the
question first time. Later, when I tried again after fifteen minute I was
able to send my question. That was the reason I sent it again. I am sorry
If it went two times.

Purnima Sharma

 

[Guest]

Thanks John, It is perfect!

Purnima

Answered in your prior posting. 15 minutes between posting the same
question is a bit impatient. This forum is manned by volunteers and
responses can take a while (sometimes forever).

You did a good job of posting your question and your attempted solution. As
a consequence, it is easier to determine what you want and suggest possible
solutions.