Why do you need to do this in a query? If you just need the result for a
report it is very simple to do in the report.

Using sorting and grouping to group by badge number
Add a control to the detail line
Set the control's source to =1
Set the control's running sum property to Over Group
Run the report and you should see each line get a number from 1 to N and each
new group will restart at one.

In a query, you can use one of several methods. The one I would use in this
case is shown below.

SELECT [Badge No], DepLastName, DepFirstName
, 1+(SELECT Count(*) FROM [YourTable] as Temp WHERE Temp.[Badge no] =
[YourTable].[BadgeNo] and Temp.DepFirstName [YourTable].DepFirstName) as The
Count
FROM [YourTable]
ORDER BY [Badge No], DepFirstName

In query design view you would have to enter the expression
1+(SELECT Count(*) FROM [YourTable] as Temp WHERE Temp.[Badge no] =
[YourTable].[BadgeNo] and Temp.DepFirstName [YourTable].DepFirstName)
into a field "cell".

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

Bill B. wrote:
Hello,

I have a query that returns these results for me:

Badge No DepLastName DepFirstName
11111 Smith Mary
11111 Smith Joe
22222 Brown Bill
22222 Brown Mary
22222 Brown Jack

I need to count the dependents that are linked to each Badge No so my
results look like this

Badge No Count DepLastName DepFirstName
11111 1 Smith Mary
11111 2 Smith Joe
22222 1 Brown Bill
22222 2 Brown Mary
22222 3 Brown Jack

I don't have much experience with modules and functions to accomplish this.
Is there anyway this can be done. Thanks!

Bill B.