Prev: creating a flag to identify first, second time etc someone has app
Next: Append Query with Oldest top Records by Group
From: John Spencer on 19 Apr 2010 19:59
Sounds as if you need a ranking query. You can use a calculated expression to
get the ranking. This is not the most efficient method but it is good enough
for small recordsets.

Field: Flag: 1 + DCOUNT("*","YourTable","[Name]=""" & [Name] & """
AND [Date of Procedure]+ [Time of Procedure]>=#"
& [Date of Procedure]+ [Time of Procedure] & "#")


SELECT [Name], [Date Of Procedure], [Time of Procedure]
, 1 + DCOUNT("*","YourTable","[Name]=""" & [Name] & " AND [Date of Procedure]+
[Time of Procedure]>=#" & [Date of Procedure]+ [Time of Procedure] & "#") as Flag
FROM YourTable

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

helpreq wrote:
> Hi,
>
> If I have a dataset showing the same person three times, on
> three different dates but
> want to create a flag (as below) which states whether this is the first time
> this person has appeared in my dataset and so on - how would I be able to do
> so?
>
> e.g
>
> Name date of procedure time of procedure flag
> john smith 15/10/2009 09:30 first
> procedure
>
> john smith 20/11/2009 10:30
> second procedure
>
> john smith 28/12/2009 10:30 third
> procedure
>
>
> Many thanks in advance
 | 
Pages: 1
Prev: creating a flag to identify first, second time etc someone has app
Next: Append Query with Oldest top Records by Group