Restarting Record Count in a Query

  • Thread starter Thread starter Bill B.
  • Start date Start date
B

Bill B.

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.
 
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
 
Try this --
SELECT [Badge No], [DepLastName], [DepFirstName], (SELECT Count(*) FROM
YourTable AS [XX] WHERE [XX].[Badge No] = YourTable.[Badge No] AND
[XX].[DepLastName] & [XX].[DepFirstName] <= YourTable.[DepLastName] &
YourTable.[DepFirstName] ORDER BY [XX].[Badge No], [DepLastName] &
[DepFirstName]) AS [Count]
FROM YourTable
ORDER BY [Badge No], [DepLastName], [DepFirstName];

I included the last and first names in combination as there may be a family
that has multiple last names.
 
Back
Top