Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Numerating Grouped Query Results
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

3 Posts

Posted - 11/07/2012 :  14:02:29  Show Profile  Reply with Quote
Hi everyone.
I need some help in building a field that will yield numerating my query results based on a grouped end result would need to look something like below...not sure if its even possible...but couldn't hurt to ask..

Agent Team WeekEnding RecordID Score Audit#
Mike RedTeam 11/03/2012 222 99 1
Mike RedTeam 11/03/2012 985 81 2
Mike RedTeam 11/03/2012 1002 87 3
Tony BlueTeam 11/03/2012 647 60 1
Tony BlueTeam 11/03/2012 788 75 2
Mike RedTeam 11/09/2012 1215 91 1
John GreenTeam 11/09/2012 1375 95 1
John GreenTeam 11/09/2012 1415 98 2

I need to be able to create the field labeled "Audit#"...the query results need to be numerated by the number of times a record is grouped (ex. Mike, RedTeam, 11/03/2012 has three recordid and so the Audit# will show 1,2,3)..Thank you in advance for any insight or direction on this issue.

Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/07/2012 :  14:05:56  Show Profile  Reply with Quote
The row_number function is ideally suited for this purpose. Available on SQL 2005 or later
	ROW_NUMBER() OVER (PARTITION BY Agent, Team, WeekEnding ORDER BY RecordId) AS [Audit#]
Go to Top of Page

Starting Member

3 Posts

Posted - 11/07/2012 :  14:13:27  Show Profile  Reply with Quote
Wow, thank you for the prompt response sunitabeck. the query i am building is in Access 2007 and can't seem to find the row_number function...thoughts?
Go to Top of Page

Flowing Fount of Yak Knowledge

2875 Posts

Posted - 11/07/2012 :  15:30:19  Show Profile  Reply with Quote
The ROW_NUMBER() function is a SQL Server function, as this is a MS SQL Server forum. To do this in Access, I think you can join a table to itself to get similar results.

SELECT t1.*,count(*)
FROM Table t1
t1.Agent = t2.Agent
and t1.Team = t2.Team
and t1.Weekending = t2.Weekending
and t1.RecordID >= t2.RecordID


Everyday I learn something that somebody else already knew
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000