creating a flag to identify first, second time etc someone has app

[helpreq]

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

 

[KARL DEWEY]

You need to combine the data in [date of procedure] and [time of procedure]
into a single DateTime field. I suggest using a number datatype for the flag
field then an update query could add to the maximum flag for the person.

 

[John Spencer]

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