Autonumbering base on the same item on field

[dhen21dx]

Hello, just want to ask if we can have an autonumbering of same item in fields, the scenario is that i have a query to append on the table, but i need autonumber the item that is same on 1 field.

Autonumber	Item	Description
1​	Orange	sssss
2​	Orange	xxcccc
1​	Banana	qwd
1​	Apple	wqdxqwd
3​	Orange	qdwqw
2​	Apple	qwdqwsdwqw

 

[davidvorob]

out of curiosity, what do you need that for? If you have an autonumber, you can always create a query that groups by item and sorts by the autonumber you gave, or does any other functionality.

 

[dhen21dx]

My purpose of it is that making a version on that specific field(key identifier), let say there is/are a change/s on other fields then we can trace the version of it where it changed.

 

[davidvorob]

couldn't you still do that with a regular auto-number? It wouldn't be 1,2, and so on for Oranges, but more like 1, 5, 21, or whatever. Point is it is still sorted in numerical order and when you do a sort by, you will get equivalent results.

 

[dhen21dx]

No, because the query i created consist on different let say fruits, we can also introduce new fruits that is not yet in the history table. I mean i need to identify the field from the history and its last number so i can make a new lastnumber on current one. let say here are example.

Making Orange(current query) as 4 append to History table, Apple as 3 and Grapes as 1..

History Table

Autonumber	Item	Description
1	Orange	sssss
2	Orange	xxcccc
1	Banana	qwd
1	Apple	wqdxqwd
3	Orange	qdwqw
2	Apple	qwdqwsdwqw

(Current query) Item to append on History table

Item	Description
Orange	rrrrrrrrr
Apple	xxxxxxxx
Grapes	ddddaa

 

[Micron]

if we can have an autonumbering of same item in fields

You could do this in a query, which would make this dynamic, but not in a table as an autonumber. I'm not understanding the need though, so if you must store these values for some sort of history reason you will have to use a regular number field (integer or long) as a table field. When adding a new instance of a "fruit" value, your append query would find the Max value for that fruit, add 1 to it, and write that value along with the fruit.

 

[dhen21dx]

Hello,

So assigning the number will be on the query and make the table as data type number only right?, then my criteria in query will be if Current_table (field 1) = History_Table (field 1), then Max (field_version)+1. Am i correct?

 

[xenou]

That is correct. So far none of your explanations of why you need this is very convincing though - its a more complicated to way to do an insertion and makes it dependent on using the right insertion query all the time. That said, it will work of course and the query is not hard to write, just not as simple as autonumbering or using a datetime field to keep track of when things were inserted.