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.
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?
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
Numerating Grouped Query Results
Posted - 11/07/2012 : 14:02:29
