Need code to obtain the first record for each memID!! 4

[coder30]

Hello,

I have a table that looks like this...

memid contact title
12345 sheila brown admin
12345 tammy lewis CFO
12345 john fesh Director
22333 james smith Manager
22333 lois william Nurse
33444 lisa turner CEO

I need to only return the first record for each memid. I can't figure out a way to do it in a query, and I haven't found a VBA code example. Help with the code is greatly appreciated!

Thanks,

 

[Remou]

What is 'first'?

 

[coder30]

First is the actual 1st record shown for each memberID. I need the first contact and title listed for each member number.

 

[Remou]

Access does not have firsts of that nature, you can use a variety of techniques to return one record from a group, (eg Select Distinct) but unless you have some way of saying which is first, it will return whatever record is handy.

 

[coder30]

Well, each record is already distinct/unique because the contact names and titles are different for each memID, so is there not some way I can use the RecordSet commands to move through the recordset and print only when it moves to a new MemID?

 

[Remou]

This may suit:
[tt]SELECT Table1.MemID, Table1.Contact, Table1.Title
FROM Table1
WHERE (((Table1.Contact) In (Select Top 1 Contact From Table1 A Where A.MemID=Table1.memID)));[/tt]

 

[Golom]

Select A.memid, A.contact, A.title

From myTable As A

Where A.contact = (Select MIN(Contact) From myTable As X
                   Where X.memid = A.memid)

Group By A.memid

will give you one record for each memid. I don't know that its necessarily the record that you want to see however.

 

[coder30]

The contact field is text so Min didn't work.

 

[lespaul]

MIN works on my text fields just fine....?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

Another way:
SELECT A.memid, A.contact, A.title
FROM yourTable AS A INNER JOIN (
SELECT memid, First(contact) AS contact1 FROM yourTable GROUP BY memid
) AS B ON A.memid = B.memid AND A.contact = B.contact1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[coder30]

Thanks, works perfectly.