SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

QueryUser777
Starting Member

USA
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 fields...my 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.

sunitabeck
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
SELECT
	*,
	ROW_NUMBER() OVER (PARTITION BY Agent, Team, WeekEnding ORDER BY RecordId) AS [Audit#]
FROM
	YourTable;
Go to Top of Page

QueryUser777
Starting Member

USA
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

jimf
Flowing Fount of Yak Knowledge

USA
2870 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
INNER JOIN Table t2 ON
t1.Agent = t2.Agent
and t1.Team = t2.Team
and t1.Weekending = t2.Weekending
and t1.RecordID >= t2.RecordID

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.05 seconds. Powered By: Snitz Forums 2000