经典模式

SELECT  (right(val(("000001" + (1000000 & A.ID))),6)) AS 人才编号 

FROM (SELECT MAX(int(基本资料.人才编号))+1 AS ID FROM 基本资料 ) AS A （这个有点不正确）

SELECT  right(int(1000000 + ("000001" & A.ID)),6) AS 人才编号

FROM (SELECT MAX(right(int(1000000 + 基本资料.人才编号),7)+1) AS ID  FROM 基本资料) AS A

（这个是一定正确的，以000001的形式以此向上加的）

这是由年份自动加1的

SELECT year(date()) & right('0' & month(date()),2) & right('0' & day(date()),2) & (right(val((10000 + ("0001" & A.ID))),4)) AS 人员编号

FROM (SELECT MAX(戒毒人员信息档案.人员编号)+1 AS ID FROM 戒毒人员信息档案 where left(人员编号,8)=year(date()) & month(date()) & day(date())) AS A