First occurrence returned by query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone know how to create a query that returns only the first occurrence of all records that have the same value in one field? These records are adjacent because I am sorting on that field already. e.g. Four adjacent records have value X in one field and I only want the first one of these four to be returned

ctda
 
You may use the Group by Function then choose First in the "Total"
SELECT table.Name, First(table.Number) AS FirstOfNumbe
FROM tabl
GROUP BY table.Name

Edmun
MCP - Access and SQL Serve


----- ctdak wrote: ----

Does anyone know how to create a query that returns only the first occurrence of all records that have the same value in one field? These records are adjacent because I am sorting on that field already. e.g. Four adjacent records have value X in one field and I only want the first one of these four to be returned

ctda
 
One approach might be to have your query select only those fields for which
you would have duplicate values and use the UniqueValues property set to
"Yes". Not sure though, that this would select the "first" of a set of
duplicate rows (your definition, my definition, and Access' definition of
"first" may not match up).
 
Edmund

Thanks so much for your answer. I didn't know what you were referring to at first because I have never used the Total row in queries before. After playing around with it a bit, I found that what I wanted was "Group By" for the Number field and "First" for all other fields. That gave me the exact result I was looking for

Thanks again. You steered me in the right direction

ctda

----- Edmund wrote: ----

You may use the Group by Function then choose First in the "Total"
SELECT table.Name, First(table.Number) AS FirstOfNumbe
FROM tabl
GROUP BY table.Name

Edmun
MCP - Access and SQL Serve


----- ctdak wrote: ----

Does anyone know how to create a query that returns only the first occurrence of all records that have the same value in one field? These records are adjacent because I am sorting on that field already. e.g. Four adjacent records have value X in one field and I only want the first one of these four to be returned

ctda
 
Back
Top