Insert Into Statement with Line Numbers

[Cheshire101]

Thank you in advance for any help given.....I need to add the line numbers to a table, I have a SQL statement to insert the information into the table from another table.

StrSQL = "INSERT INTO tblTempEmail (Temp#,Email,Assessor,Shift,AreaAssessed,StopDate) SELECT" & _
    " Count(tbltbl2BAssessed.AssessedBY),tblEmployees.Email,tbl2BAssessed.AssessedBy,tbl2BAssessed.Shift,tbl2BAssessed.AreaAssessed," & _
    "tbl2BAssessed.StopDate FROM tbl2BAssessed INNER JOIN tblEmployees ON (tbl2BAssessed.WorkShift" & _
    "= tblEmployees.Shift) AND (tbl2BAssessed.Site = tblEmployees.Area) AND (tbl2BAssessed.AssessedBy " & _
    "= tblEmployees.Title) WHERE (((tbl2BAssessed.StopDate)<Date()) AND ((tbl2BAssessed.AssessNumber) " & _
    "Is Null) AND ((tbl2BAssessed.ByPassDate) Is Null));"

Everything works if I remove the Temp# field under INSERT and Count function under the SELECTportion. Anyone know how to add the line/row number to the table using SQL???

[ssanfu]

There is an A2K example on Roger Carlson's site at

http://www.rogersaccesslibrary.com/forum/topic309.html