I have done some internet research that offeres subqueries as a
solution for my problem, but the efficiency is not good.

I have a table with UNIT_NUMBER, YEAR, PERIOD_PROFILE.

I would like to devlop a user function, to sequentially
count the records with a given unit number and to start the count over
when the unit number changes. This number will help as I need to use a dateadd() function that will utilize the count number calculated by the user function. I want to call this function in a query.


Any help would be great. Here is the hopefuly result


UNIT_NUMBER

UNIT COUNT FUNCTION
0301 1
0301 2
0301 3
0328 1
0328 2
0328 3

jdbash1,
I have a suggestion or work around to your question.
Try this:

Select COUNT(Unit) + 1 FROM tablename.

OR

SELECT MAX(Unit_Number) + 1
WHERE unit = value
FROM tablename

Hope this helps,
john