first value

[Purnima Sharma]

I have a master team list with sales agents team codes and names. There are
multiple occurrences of same codes with names of agents spelled differently
in the master table e.g.

76AB Allison Burton
76AB Allison Borton
76AB allison burton
76AB Alison Borton

I have to create a join between two tables to pull the agent's names. The
code is the common field. I want to pull just the first occurnce of agent's
name. I used the following query:

SELECT *
FROM TblLookup_Team_Agent_Pilot as O1
where PilotCode =
(select min(PilotCode) from TblLookup_Team_Agent_Pilot as O2
where O2.PilotCode=O1.pilotCode) ;

It still captures alll the rows. Can someone help? Thanks a lot!

Purnima Sharma

 

[John Spencer]

Use your subquery as an item in the select clause and have it return the
PilotName

SELECT *
, (SELECT First(PilotName)
FROM TblLookup_Team_Agent_Pilot as O2
WHERE O2.PilotCode=O1.pilotCode) as ThePilot
FROM TblLookup_Team_Agent_Pilot as O1

Or build a query that gets just the Code and the Name one time and then use
that instead of the table in your queries.
SELECT PilotCode, First(T.PilotName) as PilotName
FROM TblLookup_Team_Agent_Pilot
GROUP BY PilotCode

Of course that may make any query you use it in incapable of being updated.

The best thing you could do would be to eliminate the duplicate entries in
tblLookup_Team_Agent_Pilot. Either through a redesign of your table
structure or through removal of the duplicate records. Without knowing your
structure it is impossible to tell you what your solution should be.

 

[Purnima Sharma]

Thanks John!
I did try my query with First function but for some reason it didn't work. I
will try using the subquesry as an item and let you know. I know my
company's database is not well designed and they use sales agents name for
every look up. Their data is all in excel and I am trying to build an Access
database. we have a very big file in Excel and it takes forever to do any
manipulation. I am trying to do all the manipulation in Access and transfer
the data to an excel file to reduce the file size. Thanks for your help.

Purnima