Fikter outrepeated values - IN JUST ONE FIELD

[Richard G]

I have a table that includes the fields Index No, Dated and Timed
The index number field contains both unique and repaeated data

When the table is sorted by Dated and Timed I'd like to be able to just have
the first instance of each actual Index No to work with. Is there a
relatively easy method

Thanks
Richard

 

[John Spencer]

Can you tell us the field types?
Assuming that Dated and Timed are DateTime fields, you could use

SELECT [Index No], Dated, Timed
FROM YourTable
WHERE Dated + Timed =
(SELECT Min(Dated + Timed)
FROM YourTable as tmp
WHERE tmp.[Index No] = YourTable.[Index No])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Richard G]

John

Many thanks for the reply. I thought I knew at least a little about select
criteria but I wouldn't have got to that until now

They are as you presume Date Time fields

Should there thus be something else to the right of the "EQUALS SIGN" in the
third line of the code or does the rest of it just follow on as the brackets
would suggest?

Richard

Can you tell us the field types?
Assuming that Dated and Timed are DateTime fields, you could use

SELECT [Index No], Dated, Timed
FROM YourTable
WHERE Dated + Timed =
(SELECT Min(Dated + Timed)
FROM YourTable as tmp
WHERE tmp.[Index No] = YourTable.[Index No])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have a table that includes the fields Index No, Dated and Timed
The index number field contains both unique and repaeated data

When the table is sorted by Dated and Timed I'd like to be able to just have
the first instance of each actual Index No to work with. Is there a
relatively easy method

Thanks
Richard

 

[Richard G]

John
A quick follow on to say all worked as I wanted
I presuned i'd have to use MIN but couldn't previously get the right MIN
when I'd grouped the records by week No

richard

Can you tell us the field types?
Assuming that Dated and Timed are DateTime fields, you could use

SELECT [Index No], Dated, Timed
FROM YourTable
WHERE Dated + Timed =
(SELECT Min(Dated + Timed)
FROM YourTable as tmp
WHERE tmp.[Index No] = YourTable.[Index No])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have a table that includes the fields Index No, Dated and Timed
The index number field contains both unique and repaeated data

When the table is sorted by Dated and Timed I'd like to be able to just have
the first instance of each actual Index No to work with. Is there a
relatively easy method

Thanks
Richard