Need update query to assign/revise employee seniority number 1

[MelissaMc]

I'm a relative novice (strong user, but little programming knowledge) working on a personnel database in which employee Seniority is determined by Hire Date then SSN.&nbsp;&nbsp;I want to use a query that sorts into the correct order, then assigns and updates the table with a 'hard' Seniority number (most senior employee = 1, etc.)&nbsp;&nbsp;Need to re-run query at intervals when employees quit/terminate.&nbsp;&nbsp;Trying to avoid having to manually key 300+ seniority numbers whenever someone quits!<br><br>Get the feeling from what I've seen in the archives that this is either impossible or at least difficult.&nbsp;&nbsp;Anyone have a solution?<br><br>TIA

 

[Chopper]

Melissa,<br><br>Create a module with a function like the following:<br><br><b>public Function RenumberSeniority()</b><br><br>dim db as dao.database, rst as dao.recordset<br>dim lngCount as long<br><br>set db = currentdb<br><font color=red>'open a recordset with the hiredate, ssn, and seniority number, sorted ascending first by hiredate, then by ssn.&nbsp;&nbsp;Be sure to insert your own field and table names.</font><br>set rst = db.openrecordset(&quot;SELECT fldHireDate, fldSSN, fldSeniorityNum FROM tblPersonnel ORDER BY fldHireDate, fldSSN;&quot<br><br><font color=red>'Loop through each record, updating the seniority number, starting with one, and incrementing by one each time.</font><br>do while not rst.eof<br>&nbsp;&nbsp;&nbsp;&nbsp;lngCount = lngCount + 1<br>&nbsp;&nbsp;&nbsp;&nbsp;rst.edit<br>&nbsp;&nbsp;&nbsp;&nbsp;rst!fldSeniorityNum = lngCount<br>&nbsp;&nbsp;&nbsp;&nbsp;rst.update<br>&nbsp;&nbsp;&nbsp;&nbsp;if not rst.eof then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.movenext<br>&nbsp;&nbsp;&nbsp;&nbsp;endif<br>loop<br><br>rst.close<br>set rst = nothing<br>set db = nothing<br><br><b>end function</b><br><br>Then you can call this function from any place in your application, either behind a button, or from a macro.<br> <p>-Chopper<br><a href=mailto: > </a><br><a href= > </a><br>

 

[DaveTom]

If you don't want to write a bunch of VBA code, there is also a solution using a query:<br><br>1. Create a query with your employee table in it twice (so it will appear as tblEmployees and tblEmployees_1).<br><br>2. DO NOT LINK THE TWO &quot;TABLES&quot;.<br><br>3. Make it an aggregation query (click the &quot;Totals&quot; button with the sigma on it)<br>4. From tblEmployees, add SSN and DateOfHire.<br>5. From tblEmployees_1, add SSN and DateOfHire<br>6. In the totals line, Group By SSN, DateOfHire of tblEmployees.<br>5. For SSN of tblEmployees_1, in the totals line choose Count. <br>6. For DateOfHire of tblEmployees_1, in the totals line choose Where.<br>7. For the field above, in the Criteria box, type &quot;&lt; [tblEmployees].[DateOfHire]&quot;<br><br>9. Run your query.&nbsp;&nbsp;The result set will show the employees ranked by seniority.&nbsp;&nbsp;You can further change it to an Update or Make Table query, as suits your needs.<br>

 

[elizabeth]

BTW, it's generally considered good practive to avoid storing calculations in tables for just the reasons yu mentioned (the calc becomes incorrect when the data it is based on changes). Normally you want to assign those numbers each time you run a form or query that depends on them. Another BTW, is your company's policy such that you won't ever run into problems with rehires, leaves of absence, etc. complicating your formula? Parental leave alone is about 90 days and could affect seniority if that is a consideration.

 

[MelissaMc]

Chopper - the code works perfectly!&nbsp;&nbsp;Many Thanks!<br><br>Dave, Elizabeth - thanks for the additional tips.<br><br>Until my next dilemma....