Automatically fill in field based on number of reiterations

[AKBirdie]

I am trying to create a field (either in my original table or in a query) that automatically fills in the visit number for each individual, so when an individual makes a subsequent visit, the visit# will automatically fill in.

ie:

IND VISIT#
a 1
a 2
a 3
b 1
b 2
c 1

...if INDIVIDUAL "a" makes another visit, VISIT# will be 4.

IND VISIT#
a 1
a 2
a 3
a 4
b 1
b 2
c 1

Thanks!

 

[randy700]

Why not normalize your data and list each individual only 1 time in the table? Then, you can update the visit number simply by adding 1 to the current value at each subsequent visit.


Randy

 

[AKBirdie]

There are a number of other fields associated with this individual other than visit number that complicates the db. It has to be this way and can't be normalized.

Any ideas sin normailzation?

 

[randy700]

Use the MAX function.

Randy

 

[jtseltmann]

Just looking at what you wrote if you did you could create a function that will create a simple recordset.

set rs = "Select Visit From tablename where Individual = 'a'"

then set the function return equal to rs.recordcount + 1

That should accomplish what you are looking for.

 

[jtseltmann]

or do the same as the above with using "Select MAX Visit from tablename where individual = 'a'" and then increment that by 1.

 

[TheAceMan1]

How are ya AKBirdie . . .

I agree with [blue]randy700[/blue], but since we don't know your table structure, how about using [blue]DMax[/blue] to get the max value for an individual and simply add 1?

[blue]Me!Visit# = DMax("[Visit#]", "TableName", "[Ind] = '" & Me![Ind] & "'")[/blue]

[blue]Your Thouhts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]