How to generate Autoincrement field through a select query?

[CJ_London]

OK - so anwser this question



in which case is there any importance as to which record is which counter - at the moment there is no means to telling them apart except the ID?

[Sana Firdaus]

We have a name text field to differentiate which record is which counter.I have attached the Database for it.

[CJ_London]

same db as before - no name field.

And to be clear - there are no name duplicates, at least not within same dept, sub dept and date. Otherwise you will have the same problem, just at a lower level

One other thought, your date field - do you have a time element as well, could use that instead

[Sana Firdaus]

Check the 17 reply,i have attached it

[CJ_London]

this should work

SELECT (SELECT Count(*) FROM Table1 C WHERE Department =T.Department AND [Sub Department Rank]= T.[Sub Department Rank] AND clng([Start Date]) & [Name]<=clng(T.[Start Date]) & t.[name]) AS [Counter], *
FROM Table1 AS T;

could be quite slow because of combining text.

Unable to test because the db seems to be corrupt, unable to edit the query


If order within the date doesn't matter then you could use the ID which should be quicker

....AND [Start Date]+[ID]/100000<=T.[Start Date] +T.[ID]/100000

what you are doing is extending the date to include another sub value - in the name example converting the date to a number and appending text so you get

1/1/2016 is stored as a number (42370) to which you append the text so you get

42370OWK-0010001: Q CSV Support for R&D Symyx ELN 6.9 Upgrade

to compare with the other values

using the ID route you would have (11 for the ID)

42370.00011

and being numbers the comparison being undertaken should be quicker

[Sana Firdaus]

Thanx for your time&help.its working with the name using as distinct ID for Dup date values.
Even though,its query is a bit slower but thank god its resolved.
Thank you very much once again Ajax :-)

[CJ_London]

no problem