I have 5 fields...

1. Appraiser Number
2. Year
3. Client
4. Appraisal Number
5. Folio Number


My problem is that the Appraisal number field has to do the following:

- the Appraisal number field is the number of appraisals that we have done for that client for that specific year. How can I create this counter? Here is an example:

Appraiser Number: 22
Year: 02
Client: 112
Appraisal Number: 317
Folio Number: 54093

This means that we have done 317 appraisals for client 112 in 2002.

Once the date changes to 03, the counter goes back to zero.

Please keep in mind that this database has over 60000 records. I'm just adding a few fields to the database.

:confused:

How are you trying to display the count? Report, Query, Form?
You can use Dcount to display the count using criteria, or you could design a count query that groups by year, and use it how you want. I would not store that information in your rows when it can be calculated.

This counter will be on a form...

Note: As soon as we enter the client number, I want a message box stating "Would you like the Appraisal Number to be 317?" If yes, then it adds 317 to the Appraisal Number.

Once the date changes to 03, the counter goes back to zero.

Please keep in mind that this database has over 60000 records. I'm just adding a few fields to the database. The old counter isn't there since the old databe was done in VB.

Try something along these lines...

Your potential appraisal number is equal to...


DCount( "[AppraisalNumber]", "Appraisals", _
"[Year] = " & CStr( DatePart("yyyy", Now)) & _
" AND [AppraiserNumber] = " & Cstr( appraiser's number) & _
" AND [ClientNumber] = " & Cstr(client's number) ) + 1


Where you put this depends on where you are going to store the field. But this is the general idea.