Writing a Query to Identify "First" Value

[EBox]

Hello:

I am running a query against a data table which has the following fields:

ID Number
Function
Event DateTime

I am looking specifically at all records where [Function] = "ADT". For these, there are sometimes mutliple occurences for each [ID Number] because this event happened several times.

I want to get the first occurence of [Function] = "ADT" for each [ID Number]. So I set up a Totals Query to group by [ID Number] and pull the First [Event DateTime] where [Function] = "ADT".

When I ran this query, I found that sometimes (not all cases) the [Event DateTime] shown was not the first one. And when I would re-run the query, sometimes the records would get corrected, and then others appeared not to pull the first occurrence of "ADT".

Very strange, because I did not modify the query structure at all.

Can anyone offer any thoughts as to why this is happening? I would really hate to have to manually look at this in Excel.

Many thanks,
E Box

 

[PHV]

Have you tried to use the Min aggregate function instead of the unreliable First ?

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

 

[EBox]

Yes, I tried it both ways, and neither worked.

 

[PHV]

Any chance you could post the SQL code, some input sample data and returned result vs expected result ?

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