How to generate Autoincrement field through a select query?

[Sana Firdaus]

I have a table with following fields department,sub department rank and start date?I want to create an autoincrement count field based on ascending start date and group by on Department and sub department rank.For example shown for 1st 6 records ,the count field.is there any way to automate this thing?please help

Department	Sub department Rank	Start Date	Count
HR	1.00	11/17/2014	2
HR	1.00	11/29/2011	1
HR	1.00	4/1/2015	3
HR	1.00	7/1/2015	4
HR	1.00	7/6/2015	5
HR	1.00	8/17/2015	6
HR	2.00	5/20/2013
HR	2.00	8/15/2013
HR	2.00	3/16/2014
HR	2.00	4/1/2014
HR	2.00	8/25/2014
HR	2.00	9/1/2014
HR	2.00	10/1/2014
HR	2.00	11/20/2014
HR	2.00	11/24/2014
HR	2.00	1/1/2015
HR	3.00	5/7/2015
HR	2.00	1/27/2015
HR	4.00	1/5/2015
HR	2.00	2/2/2015
HR	5.00	6/18/2014
HR	2.00	2/13/2015
HR	5.00	7/1/2015
HR	2.00	5/4/2015
HR	5.00	1/1/2015
HR	2.00	5/27/2015
HR	5.00	3/15/2014
HR	2.00	6/22/2015
HR	5.00	1/27/2015
HR	2.00	10/12/2015
HR	5.00	3/4/2015
HR	2.00	10/15/2015
HR	5.00	11/24/2014
HR	2.00	1/11/2016
HR	5.00	4/13/2015
HR	6.00	12/10/2013
HR	6.00	3/1/2015
HR	6.00	7/29/2015
HR	6.00	11/9/2015
HR	7.00	5/28/2014
HR	8.00	1/20/2014
HR	8.00	7/1/2014
HR	9.00	5/25/2015
HR	9.00	2/6/2014
HR	9.00	7/1/2014
HR	9.00	12/15/2013
HR	9.00	1/1/2013
HR	9.00	4/1/2014
HR	9.00	12/8/2014
HR	10.00	2/1/2015
HR	11.00	5/11/2015
IT	1.00	1/1/2015
IT	1.00	1/20/2014
IT	1.00	1/1/2015

[aytee111]

Only a table can have that kind of field. You could either have this as an append query to a table with an autonumber field, or you can call a function from the query which will do it for you.

[Sana Firdaus]

@aytee111
But Autonumber field is based on certain condition like group by on Department and sub department rank and then autonumbering on the basis of ascending start date?
Even if i append the query of ID field,will that apply with the above needed condition?

[aytee111]

If you sort the records as you are appending them they should come out in the right order. If you always need to start the numbering from 1 then you would need a routine to :
1 - delete the append table
2 - have an unused table to copy to the table you wish to append to
3 - run your query

The only true way to control it is to have a function, altho the above will work - usually.

[Sana Firdaus]

@aytee111 Thanks for your help,one thing I wanted to ask like how can I achieve this auto increment field based on certain field grouping through a function?

[aytee111]

I think I didn't get it - when you mean "group by" do you mean that you want to start from number one again for each department/subdept? Your example doesn't show.

[Sana Firdaus]

Department Sub department Rank Start Date Count
HR 1.00 11/17/2014 2
HR 1.00 11/29/2011 1
HR 1.00 4/1/2015 3
IT 1.00 1/1/2015 4
IT 2.00 1/20/2014 6
IT 1.00. 1/20/2015. 5
IT. 2.00. 7/20/2015. 7



Given is the example,first I sort one Department and under that 1,2,3... sub department ranking is present ,with each ranking containing several start date as shown for HR deptt under 1 sub rank there are 3 start date,so the thing is count will start from 1 with ascending/oldest start date and it will increment accordingly.Then I filter the second deptt IT ,filter sub deptt ranking1 and then have count field not from the start 1 but from the last HR department field that means it wud start with 4 and so on,then again filter on subranking 2 and put the count as per ascending start date like 6 and 7 shown in example.
I am stuck in this thing from long.Thanks.

[aytee111]

Here is some code that will do it. Note that you need to run the query from inside VBA in order that the count starts at 1 each time. Go into the VBA editor and insert a new module.

Option Compare Database
Option Explicit

Public DeptCount As Long

Public Function RunQuery()

    DeptCount = 0
    DoCmd.OpenQuery "Query1"
    
End Function

Public Function GetCount(Department)

    DeptCount = DeptCount + 1
    GetCount = DeptCount
    
End Function

This is the query:
SELECT Table1.Department, Table1.SubDept, Table1.StartDate, GetCount([Department]) AS DC
FROM Table1
ORDER BY Table1.Department, Table1.SubDept, Table1.StartDate;

[Sana Firdaus]

Thanks aytee,i applied the VBA Code but the value of DC is incrementing automatically and goes upto high even when the module is run only once.What i need is for each department and under it for each sub department rank,it numbers from 1,2,3... as per ascending start date and then for the same deptt with different sub deptt rank,it again starts it count from 1 ,2,3,...

[CJ_London]

you can do it in a query

from your sample data in post #7

SELECT *, (SELECT Count(*) FROM myTable C WHERE Department =T.Department AND Subdepartment T.Subdepartment AND RankStartDate<=T.RankStartDate) AS Counter
FROM myTable T

[Sana Firdaus]

Ajax,it's working but the thing is it's incrementing without taking considering sub department ranking order like for eg under one Department HR,there are multiple 1,2,3 sub deptt ranking.For each sub deptt ranking let's take 1,want 1,2,3... in counter wrt ascending start date and then for subranking 2 ,again the counter will start from 1,2,3 etc not like 1,2,3 for sub ranking 1 and then 4,5,6 for subranking 2.Here is the eg:

Department. Sub deptt start date counter
HR. 1. 2014/01/10. 3
HR. 1. 2013/03/10. 2
HR. 1. 2011/04/10. 1
HR. 2. 2015/04/11. 1
HR. 2. 2016/07/10. 2
IT. 1. 2013/04/07. 1
IT. 1. 2016/06/07. 2

[CJ_London]

due to spaces in your column headings, it was not clear what word belonged to what column, also looks like I missed an = sign (but then the query would have failed), change to

SELECT *, (SELECT Count(*) FROM myTable C WHERE Department =T.Department AND SubdepartmentRank= T.SubdepartmentRank AND StartDate<=T.StartDate) AS Counter
FROM myTable T

if that is still not working as required, post what you have actually used as code, not what I have provided

[Sana Firdaus]

Hi Ajax,
The query is working but for some department like HR and others,under sub department ranking,the counter is not following the increment pattern 1,2,3,4...,it is skipping the increment for eg for Department HR ,under sub department rank 3,counter has started its increment from 1,2,3 and directly jumped to 8.
Attached is the extract database including the table data and the query applied of yours.

[CJ_London]

It is because you have the same date for hr dept 3. Deduping the table will resolve it

Alternatively,
1. Do you want to see the same date more than once but each with its own counter value? 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?
2. Or do you want to filter out the multiple copy records and just display 1 - in which case, same question about ID to display - either don't display it, or display the lowest or display the highest?
3. depending on requirement, leave as is - they all have the same date so their 'ranking' will be the same. You could change the <= to = and add 1 to display the lower value i.e. 1,2,3,4, 4,4,...9

which do you want?

[Sana Firdaus]

@Ajax Yes,i got it because of the same date we are having repititive counter value,but is it possible to show each same date with its own counter value.I want to have distinct counter for each subdepartment rank starting from 1,2,3,4......etc even for the same date.
Thanks
Sana