A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Restarting Record Count in a Query



 
 
Thread Tools Display Modes
  #1  
Old March 3rd, 2010, 03:27 PM posted to microsoft.public.access.queries
Bill B.
external usenet poster
 
Posts: 19
Default 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  
Old March 3rd, 2010, 03:53 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default 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  
Old March 4th, 2010, 12:39 AM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:18 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.