Please help in Autoincrement query?

[Sana Firdaus]

I have a table with fields name,Function,start date, sub function rank ,Sub function with data as below.I have attached the data extract as well.

What i want to generate is a counter field which filters the function and after then filters one of the subfunction rank starting from 1 and then sorts the data as per ascending start date and then assigns 1 to the oldest start date,2 to the second oldest ,3,4 and so on.Similarly,for the second sub function ranking with same function,it starts the numbering from where it has left previously.For ex-for the Function "ECE" and then filtering by sub function ranking 1 ,counter starts from 1,2, and ends up at 14 as per ascending start date ,so for the sub function ranking 2 it should start from 15 ,16 and so on arranged in ascending start date.

Name	Function	Start Date	Sub Function Rank	Sub Function	Counter
2015 CLS: CAPA/NC/Audit	Events	1/4/2016	2.00	Cult	4
EtQ: Add New BME Site for DePuy Synthes	Events	11/2/2016	2.00	Cult	5
EtQ Instinct - FSL(4 Sites): - CAPA/NC/Audit - IT Platform	Events	11/28/2016	2.00	Cult	6
ETQ Access For NeuWave Medical	Events	1/2/2017	2.00	Cult	7
ETQ: to CSS/Ethicon Supplier Portal	Events	1/16/2017	2.00	Cult	8
IN:47973 NICE (Nonconformance, Issue & CAPA across the Enterprise)	Events	6/1/2015	1.00	Music	1
ETQ Instinct: SDDC Migration	Events	1/16/2017	2.00	Cult	9
Combo Products - complaints	Events	8/20/2015	3.00	Pra	14
Geel: API Small – NICE	Events	9/19/2016	1.00	Music	2
ETQ & Cognos Enhancements	Events	2/20/2017	2.00	Cult	10
IT_BB: Supply Chain Quality Apps Base Business	Events	9/1/2016	3.00	Pra	15
IN:56561 EtQ Symphony Compliance Release Management [1]	Events	11/29/2016	3.00	Pra	16
Audit Module request from Janssen Vaccines Corp.	Events	3/31/2017	2.00	Cult	11
ETQ Divestiture & Acquisitions	Events	4/10/2017	2.00	Cult	12
LRFP - ETQ Instinct Platform	Events	2/12/2018	2.00	Cult	13
ETQ Symphony Compliance Release Management 2017-2018	Events	2/28/2017	3.00	Pra	17
IN:56561 LRFP EtQ Symphony Compliance Release Management	Events	1/8/2018	3.00	Pra	18
JJQSS LRFP Consumer NC & CAPA Migration to NICE (EtQ-S to TW ETS)	Events	2/4/2019	1.00	Music	3
IT: SDDC Symphony EtQ Database Migration [1]	Events	2/19/2018	3.00	Pra	19
BB-QSE-Sys=TruVAULT (OWK-0016840)	ECE	8/31/2015	1.00	Control	1
OWK-0013401 Global Customs Recordkeeping Solution	ECE	2/1/2016	1.00	Control	2
Global Customs Recordkeeping Solution	ECE	2/1/2016	1.00	Control	3
Tru Vault: Safety/Regulatory Validation Doc Repos, JRD	ECE	10/17/2016	1.00	Control	4
TruVault: for Sterility Assurance (Transition from SharePoint)	ECE	10/31/2016	1.00	Control	5
Quality Systems and Services (JJQS)	ECE	11/28/2016	1.00	Control	6
truVAULT Program: Enhancement Release for truVAULT/truVAULT Viewer	ECE	1/2/2017	1.00	Control	7
TruVault: Selzach DC - Doc Mgmt IT Solution	ECE	1/3/2017	1.00	Control	8
BB-QSE-Sys=docspace (OWK-0009896)	ECE	1/2/2012	2.00	Space	15
Tru Vault: for Execution Systems Standards	ECE	1/16/2017	1.00	Control	9
Onboard MD Medical Safety CoE to TruVault to maintain their procedures	ECE	1/16/2017	1.00	Control	10
DocSpace: Storage of LM Supplier-Material Qualification (SMQ) Documentation	ECE	10/14/2016	2.00	Space	16
TruVault - Trade Compliance	ECE	1/25/2017	1.00	Control	11
TruVault - S. Africa DC's - Doc Mgmt IT Solution	ECE	1/30/2017	1.00	Control	12
BQRC Migration from RNDDOCS to truVAULT	ECE	6/5/2017	1.00	Control	13
BB : DocSpace Program : Maintain & Sustain	ECE	4/29/2017	2.00	Space	17
LRFP: truVAULT Program	ECE	1/1/2018	1.00	Control	14

Please help.

[ranman256]

Make a table same as your data,but with an autoNum field, tSorted.
And make a copy of tSorted, to TSorted-MT.
The process is, copy the tSorted-MT to tSorted. (To preserve the autoNum)
append your sorted data into the tSorted table.

[Sana Firdaus]

Hi ranman,

i want to generate the Autonumber as per the Counter Field given for reference.