If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Restarting Record Count in a Query
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. |
#2
|
|||
|
|||
Restarting Record Count in a Query
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. |
#3
|
|||
|
|||
Restarting Record Count in a Query
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. -- Build a little, test a little. "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. |
Thread Tools | |
Display Modes | |
|
|