MS Access Adding Sequential Numbering
in [General]
|
From: Laura1 via AccessMonster.com on 9 Apr 2010 15:33 I have a table with the following data Deal ID 1234 Assoc 1234 Associate Role ABC Deal ID 1234 Assoc 4567 Associate Role ABC Deal ID 1234 Assoc 8910 Associate Role DEF I want to number the like roles for each Deal Id so the result would be Deal ID 1234 Role ABC 1 and then Deal ID 1234 and Role ABC 2, Deal ID 1234 Role DEF 1 Is there a way to assign a numbering based on Deal id and role? Thank you! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201004/1
From: Paul Shapiro on 9 Apr 2010 15:46 "Laura1 via AccessMonster.com" <u31091(a)uwe> wrote in message news:a6495bdd3c50c(a)uwe... > I have a table with the following data > > Deal ID 1234 > Assoc 1234 > Associate Role ABC > > Deal ID 1234 > Assoc 4567 > Associate Role ABC > > Deal ID 1234 > Assoc 8910 > Associate Role DEF > > I want to number the like roles for each Deal Id so the result would be > Deal > ID 1234 Role ABC 1 and then Deal ID 1234 and Role ABC 2, Deal ID 1234 Role > DEF 1 > > Is there a way to assign a numbering based on Deal id and role? Something like this sql should get you the sequence numbers: Select A.dealID, A.role, A.associateID, 1 + count(B.dealID) as sequenceNumer From YourTable as A Left Join YourTable as B On B.dealID=A.dealID And B.role=A.role And B.associateID < A.associateID Group By A.dealID, A.role, A.associateID This arbitrarily assumes you want the sequence ordered by the associateID number.
From: Laura1 via AccessMonster.com on 9 Apr 2010 16:15 Thanks this is really over my head...sorry... I get the count of each like Deal id 1234 Role ABC 3 Deal id 1234 Role DEF 1 but I still can't get 1, 2, 3, for each deal id 1234 ABC combination and then deal id 1234 Role DEF 1 So it would look like this: 1234 abc 1 1234 abc 2 1234 abc 3 1234 def 1 Thanks again! Paul Shapiro wrote: >> I have a table with the following data >> >[quoted text clipped - 16 lines] >> >> Is there a way to assign a numbering based on Deal id and role? > >Something like this sql should get you the sequence numbers: >Select A.dealID, A.role, A.associateID, 1 + count(B.dealID) as sequenceNumer >From YourTable as A > Left Join YourTable as B > On B.dealID=A.dealID > And B.role=A.role > And B.associateID < A.associateID >Group By A.dealID, A.role, A.associateID > >This arbitrarily assumes you want the sequence ordered by the associateID >number. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201004/1
From: John W. Vinson on 10 Apr 2010 16:02 On Fri, 09 Apr 2010 19:33:33 GMT, "Laura1 via AccessMonster.com" <u31091(a)uwe> wrote: >I have a table with the following data > >Deal ID 1234 >Assoc 1234 >Associate Role ABC > >Deal ID 1234 >Assoc 4567 >Associate Role ABC > >Deal ID 1234 >Assoc 8910 >Associate Role DEF > >I want to number the like roles for each Deal Id so the result would be Deal >ID 1234 Role ABC 1 and then Deal ID 1234 and Role ABC 2, Deal ID 1234 Role >DEF 1 > >Is there a way to assign a numbering based on Deal id and role? > >Thank you! What's the context, Laura? Do you want to have a sequential number permanently assigned and stored in the table? If a record in a deal needs to be deleted or changes its role, do you want all the rest of the numbers for that deal to automatically adjust? If this is needed only for a report, you can put a textbox on the report with a control source of =1, and set the textbox's Running Sum property to "Over Group". If that's not adequate please post back with an explanation of what this number means and how it will be used. -- John W. Vinson [MVP]
From: Tom Wickerath AOS168b AT comcast DOT on 10 Apr 2010 16:12
Hi Laura, > Thanks this is really over my head...sorry... Paul gave you a SQL (Structured Query Language) statement, ie. a query. To make use of what he gave you, create a new query. Dismiss the Add Table dialog, without adding any tables. Then change to SQL View. You should see the SQL keyword SELECT highlighted. Backspace over this to remove it. Then copy the SQL statement that Paul gave you, and paste it into the SQL View. Notes: 1.) Use square brackets for any field names that include spaces, special characters, or reserved words. 2.) Substitute "YourTable" in the SQL statement with the actual name of your table. Use square brackets if your table name includes spaces, special characters or is a reserved word. SELECT A.AssociateID, A.[Deal ID], A.[Associate Role], 1 + count(B.[Deal ID]) AS [Sequence Number] FROM YourTable AS A LEFT JOIN YourTable AS B ON (B.[Deal ID]=A.[Deal ID]) AND (B.[Associate Role]=A.[Associate Role]) AND (B.AssociateID < A.AssociateID) GROUP BY A.[Deal ID], A.[Associate Role], A.AssociateID; I just created a quickie sample based on Paul's SQL statement, and it works just fine. If you need, I can post a zipped copy and provide a download link to you. Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ __________________________________________ "Laura1 via AccessMonster.com" wrote: > Thanks this is really over my head...sorry... > > I get the count of each like > > Deal id 1234 > Role ABC 3 > Deal id 1234 > Role DEF 1 > > but I still can't get 1, 2, 3, for each deal id 1234 ABC combination and then > deal id 1234 Role DEF 1 > > So it would look like this: > > 1234 abc 1 > 1234 abc 2 > 1234 abc 3 > 1234 def 1 > > Thanks again! > > Paul Shapiro wrote: > >> I have a table with the following data > >> > >[quoted text clipped - 16 lines] > >> > >> Is there a way to assign a numbering based on Deal id and role? > > > >Something like this sql should get you the sequence numbers: > >Select A.dealID, A.role, A.associateID, 1 + count(B.dealID) as sequenceNumer > >From YourTable as A > > Left Join YourTable as B > > On B.dealID=A.dealID > > And B.role=A.role > > And B.associateID < A.associateID > >Group By A.dealID, A.role, A.associateID > > > >This arbitrarily assumes you want the sequence ordered by the associateID > >number. > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access/201004/1 > > . > |