Generate a Sequential Number (Per Group) When Adding a Row to an Access Table

Generate a sequential number (per group) when adding a row to an Access table

I suspect that you are looking for something like this:

Dim con As ADODB.Connection, cmd As ADODB.Command, rst As ADODB.Recordset
Dim newNum As Variant

Const fLabel_Location = "O'Hare" ' test data

Set con = New ADODB.Connection
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Public\Database1.accdb;"

Set cmd = New ADODB.Command
cmd.ActiveConnection = con
cmd.CommandText = "SELECT MAX(Number) AS maxNum FROM Articles WHERE Location = ?"
cmd.CreateParameter "?", adVarWChar, adParamInput, 255
cmd.Parameters(0).Value = fLabel_Location
Set rst = cmd.Execute
newNum = IIf(IsNull(rst("maxNum").Value), 0, rst("maxNum").Value) + 1
rst.Close

rst.Open "Articles", con, adOpenDynamic, adLockOptimistic, adCmdTable
rst.AddNew
rst("Location").Value = fLabel_Location
rst("Number").Value = newNum
rst.Update
rst.Close
Set rst = Nothing
Set cmd = Nothing
con.Close
Set con = Nothing

Note, however, that this code is not multiuser-safe. If there is the possibility of more than one user running this code at the same time then you could wind up with duplicate [Number] values.

(To make the code multiuser-safe you would need to create a unique index on ([Location], [Number]) and add some error trapping in case the rst.Update fails.)

Edit

For Access 2010 and later consider using an event-driven Data Macro and shown in my other answer to this question.

Generate a sequential number when adding a row to an Access table

In Access 2010 and later you can accomplish that by using an event-driven "Before Change" Data Macro like this:

Sample Image

For more information see

Create a data macro

How to add Row Numbers and GROUP BY in Access?

You can wrap your entire query in a subquery:

select
B.*
from
( SELECT
A.*,
(SELECT COUNT(*) FROM Tabel1 WHERE A.ID>=ID) AS RowNum
FROM Tabel1 AS A
) as B
ORDER BY B.ID;

From here, you can do joins as though the output of your query was a table. I have no idea what you actually want to group on, but here is an example:

select
B.Field1, count (*) as count, max (B.RowNum) as max_row
from
( SELECT
A.*,
(SELECT COUNT(*) FROM Tabel1 WHERE A.ID>=ID) AS RowNum
FROM Tabel1 AS A
) as B
group by
b.Field1

-- Edit 11/14/2016 --

I think I see now. In that case, first build a query to handle your grouping. This is just an example:

SELECT Field1, min (Id) as min_id, max (id) as max_id
FROM Table1
group by Field1

Name this query Table1_Summary for the purposes of our example.

Now, in your new query, you will refer to Table1_Summary in the exact same way you did with your example:

SELECT 
t.*,
(select count (*) from Table1_Summary t2 where t.Field1 >= t2.Field1) as RowNum
FROM Table1_Summary t

You could theoretically do this in a single query, but for readability/maintainability sake, I'd recommend you keep them split. Here is an example of the output:

Sample Image

Inserting a rank in sequential order for grouped records in a table

I found it! No need to keep looking. The following loop routine is exactly what i needed to resolve this problem. Applying this to my 'generic/theory' example above, you can see how the 'sequence' field above is reflected by the field 'ID_Event' in the below routine. The loop checks the prior record to and if it is has the same date ('Effective Date') it adds 1 to the field 'ID_Event'

Private Sub Comando197_Click()

Dim sql As String
Dim varEffective_Date As Date
Dim NumberOfTimes
Dim varID_Event As Integer

sql = "select ID_Event,ID_ValueAddWaste,Effective_Date,ValueAddType,ValueAddType02 from tblPerformanceTrackingMaster03 where tblPerformanceTrackingMaster03.[ID_Event]=0 order by ID"

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockPessimistic

Dim counter As Integer
counter = 1

Do While Not rs.EOF

If counter > 1 Then

If varEffective_Date = rs!Effective_date Then
rs!ID_Event = varID_Event + 1

End If
End If

varEffective_Date = rs!Effective_date
varID_Event = rs!ID_Event

rs.MoveNext
counter = counter + 1

Loop
rs.Close
Set rs = Nothing

End Sub

How to add sequenced number based on sorted value in query in Access

Assuming a table name of table1, The following should yield the desired result:

select a.att1, (select count(*) from table1 b where b.att1 <= a.att1) as att2
from table1 a;

For every record, the query calculates the number of records less than or equal to the current record, which is then output as the sort index.

Generate sequence within sub group in data.table

We group by 'id1', sort the 'val' and then create 'grp' as row_number()

input %>%
group_by(id1) %>%
mutate(val = sort(val), grp= row_number())

Or another option is to arrange

input %>%
arrange(id1, val) %>%
group_by(id1) %>%
mutate(grp = row_number())

Or using data.table

library(data.table)
setDT(input)[, c("grp", "val") := .(seq_len(.N), sort(val)), by = id1]
input
# id1 val grp
#1: 1 1 1
#2: 1 2 2
#3: 1 3 3
#4: 1 4 4
#5: 2 3 1
#6: 2 4 2
#7: 2 5 3

If we need to sort as well, use setorder based on the 'id1' and 'val' to order in place, then create the 'grp' as the rowid of 'id1'

input <- data.frame("id1"=c(1,1,1,1,2,2,2),val=c(2,3,4,1,4,3,5), 
achar=c('a','a','b','b','d','c','e'))
setorder(setDT(input), id1, val)[, grp := rowid(id1)][]
# id1 val achar grp
#1: 1 1 b 1
#2: 1 2 a 2
#3: 1 3 a 3
#4: 1 4 b 4
#5: 2 3 c 1
#6: 2 4 d 2
#7: 2 5 e 3

Microsoft Access query returning sequential numbers

You can use a simple Cartesian Query having a table with numbers from 0 to 9:

SELECT 
t1.Number +
t2.Number * 10 +
t3.Number * 100 +
t4.Number * 1000 As Factor
FROM
tblNumber AS t1,
tblNumber AS t2,
tblNumber AS t3,
tblNumber AS t4

or - if you only need small series - just a table with numbers from 0 to 99.

Then for your samples:

SELECT 
10 + 0.1 * [Number] AS [Value]
FROM
tblNumber
WHERE
[Number] Between 1 And 5

and:

SELECT 
[Number] * 10 AS [Value]
FROM
tblNumber
WHERE
[Number] Between 2 And 10
ORDER BY
[Number] * 10 Desc

and:

SELECT 
DateAdd("h", [Number], #2015-04-10 12:00 PM#) AS [Date]
FROM
tblNumber
WHERE
[Number] Between 0 And 6

Addendum

A number series can also be built without a dedicated number table by using a system table as source:

SELECT DISTINCT 
[Tens]+[Ones] AS Factor,
10*Abs([Deca].[id] Mod 10) AS Tens,
Abs([Uno].[id] Mod 10) AS Ones
FROM
msysobjects AS Uno,
msysobjects AS Deca;

create sequential number column index into table with data

To add an AutoNumber field to an existing table, simply open the table in Design View, type in the Field Name and select "AutoNumber" from the drop-down list for the Data Type:

AutoNumber.png

Access will populate the new field with AutoNumber values for any existing records in the table.

Edit re: influencing the order in which AutoNumber values are applied to existing records

As with many other database operations, there is essentially no guarantee that Access will use any particular order when assigning the AutoNumber values to existing records. However, if we look at a couple of examples we can see how Access will likely do it.

Consider the following sample table named [Events]. The rows were entered in random order and there is no primary key:

EventDate   Event         
---------- --------------
2012-06-01 it's June
2012-10-01 it's October
2012-09-01 it's September
2012-12-01 it's December
2012-11-01 it's November
2012-07-01 it's July
2012-04-01 it's April
2012-08-01 it's August
2012-02-01 it's February
2012-01-01 it's January
2012-03-01 it's March
2012-05-01 it's May

Now we'll simply add an AutoNumber field named [ID] using the procedure above. After that has been done

SELECT * FROM Events ORDER BY ID

returns

EventDate   Event           ID
---------- -------------- --
2012-06-01 it's June 1
2012-10-01 it's October 2
2012-09-01 it's September 3
2012-12-01 it's December 4
2012-11-01 it's November 5
2012-07-01 it's July 6
2012-04-01 it's April 7
2012-08-01 it's August 8
2012-02-01 it's February 9
2012-01-01 it's January 10
2012-03-01 it's March 11
2012-05-01 it's May 12

Now let's revert back to the old copy of the table and see if the existence of a primary key makes a difference. We'll make [Event Date] the primary key, save the changes to the table, and then add the [ID] AutoNumber field. After that is done, the select statement above gives us

EventDate   Event           ID
---------- -------------- --
2012-06-01 it's June 1
2012-10-01 it's October 2
2012-09-01 it's September 3
2012-12-01 it's December 4
2012-11-01 it's November 5
2012-07-01 it's July 6
2012-04-01 it's April 7
2012-08-01 it's August 8
2012-02-01 it's February 9
2012-01-01 it's January 10
2012-03-01 it's March 11
2012-05-01 it's May 12

Hmmm, same thing. So it looks like the AutoNumber values get assigned to the table in natural order (the order in which the records were added to the table) even if there is a primary key.

Okay, if that's the case then let's use a make-table query to create a new copy of the table in a different order

SELECT Events.EventDate, Events.Event 
INTO Events2
FROM Events
ORDER BY Events.EventDate;

Now let's add the [ID] AutoNumber field to that new table and see what we get:

SELECT * FROM Events2 ORDER BY ID

returns

EventDate   Event           ID
---------- -------------- --
2012-01-01 it's January 1
2012-02-01 it's February 2
2012-03-01 it's March 3
2012-04-01 it's April 4
2012-05-01 it's May 5
2012-06-01 it's June 6
2012-07-01 it's July 7
2012-08-01 it's August 8
2012-09-01 it's September 9
2012-10-01 it's October 10
2012-11-01 it's November 11
2012-12-01 it's December 12

If that is the order we want then we can just delete the [Events] table and rename [Events2] to [Events].



Related Topics



Leave a reply



Submit