Number Based on Instance of Value

[CPGDeveloper]

Hi Everyone~

There must be a simple way to do this -- If I have a table as follows:

Field1
A
A
A
B
B
B

I want to create a field that counts the instances of the value in Field 1...something like this --

Field1, Field2
A,1
A,2
A,3
B,1
B,2
B,3

Any help would be greatly appreciated. Thanks.

 

[Joe4]

The reason there really isn't a "simple" way to do this, is because it isn't really an relational database type of thing. By definition, records in a table in a relational database are supposed to be totally unrelated to each other, meaning that order should really have no meaning (hence, this is more of an "Excel" type of task than an "Access/database" type of task).

However, there are ways of doing it, though you will need to write the SQL code directly, as it involves nesting queries.
See: number records in a group, within access query

 

[CPGDeveloper]

Very helpful, thanks -- I thought perhaps there might have been some Access function I didn't know about...I was able to figure it out using SQL. Thanks!

The reason there really isn't a "simple" way to do this, is because it isn't really an relational database type of thing. By definition, records in a table in a relational database are supposed to be totally unrelated to each other, meaning that order should really have no meaning (hence, this is more of an "Excel" type of task than an "Access/database" type of task).

However, there are ways of doing it, though you will need to write the SQL code directly, as it involves nesting queries.
See: number records in a group, within access query