Selecting first row of a table

[Guest]

How can I query a table to select the first record of a group. For instance
if I have duplicate values for a client name, but the other fields for that
record contain differnt values, how can I select just the first record with
the corresponding values. Here's an example:

Client Name Date Value
ABC Co. 1/1/2006 1
ABC Co. 1/7/2006 7
ABC Co. 3/3/2006 10

How do I write a query to select only the first record for each change in
client name?

 

[mcescher]

How can I query a table to select the first record of a group. For instance
if I have duplicate values for a client name, but the other fields for that
record contain differnt values, how can I select just the first record with
the corresponding values. Here's an example:

Client Name RecordDate RecordValue
ABC Co. 1/1/2006 1
ABC Co. 1/7/2006 7
ABC Co. 3/3/2006 10

How do I write a query to select only the first record for each change in
client name?

First according to what criteria? Based on the earliest Date?

Also, Date and Value are reserved words, so they will give you problems
somewhere in your development process. I'll substitute RecordDate,
RecordValue

One way would be to use a group by clause in your SQL

SELECT [Client Name], Min([RecordDate]), Min([RecordValue])
FROM tblYourData
GROUP BY [Client Name]

HTH,
Chris M.

 

[Larry Daugherty]

Look in Access help for "TOP"

HTH