Solved How to get groups of Autonumber depending on another field value

[Khurramshaikh47]

Hi, I need help with an access query in which I already have 3 columns

GroupID: Auto Number
GroupName: Text
AccountType: Lookup

I want to get different Autonumbers for each account type in a single column.
For example in the table below I've added a column name GroupCode, having different autonumber groups for different account types:

Thank you

 

[arnelgp]

Select GroupID, GroupName, AccountType,
(SELECT Count("1") From yourTableName As T Where T.AccountType = yourTableName.AccountType And
T.GroupID <= yourTableName.GroupID ) As GroupCode
From yourTableName ORDER BY AccountType, GroupID;

 

[arnelgp]

paste that in the SQL view.
then change yourTableName with the name of your Account table.
see Query1 on this demo.

 

[Khurramshaikh47]

Select GroupID, GroupName, AccountType,
(SELECT Count("1") From yourTableName As T Where T.AccountType = yourTableName.AccountType And
T.GroupID <= yourTableName.GroupID ) As GroupCode
From yourTableName ORDER BY AccountType, GroupID;

Thank you So much arnelgp.

 

[Khurramshaikh47]

Select GroupID, GroupName, AccountType,
(SELECT Count("1") From yourTableName As T Where T.AccountType = yourTableName.AccountType And
T.GroupID <= yourTableName.GroupID ) As GroupCode
From yourTableName ORDER BY AccountType, GroupID;

Hi Arnelgp,
I am sorry for bothering you again.
Actually, I've tried this code for a query but It's not working.
I am sharing screenshots please check.
This is the Query in which I am trying the code (GroupCode2) :

The Code I am trying is:

GroupCode2: (SELECT Count("1") From ChartOfAccount As T Where T.AccountType = ChartOfAccount.AccountType And
T.ID <= ChartOfAccount.ID )

The result I am getting is:

I actually want to create a Chart of Account code for each account. So I want 2 columns or 1 for creating the serial number of Account Name, Group Name wise. and in the 2nd column, I want to combine them to get a unique code for each head of the account which describes the AccountType, GroupName, and AccountName.
Like GroupCode2 and COACode in the example below:

Thank you very much for helping me always.

 

[Khurramshaikh47]

paste that in the SQL view.
then change yourTableName with the name of your Account table.
see Query1 on this demo.

Dear, arnelgp I am waiting for your response.

 

[arnelgp]

you post your db so i can see the structure.

 

[Khurramshaikh47]

you post your db so i can see the structure.

 

[Khurramshaikh47]

you post your db so i can see the structure.

Thank you for your response. I've attached my db.

 

[Khurramshaikh47]

Dear arnelgp, Please take some of your precious time to read the following before analysing my db:
I created a GroupCodeQ query to generate sequential numbers by account group and named that field GroupCode0, and then concatenated the AccountID and GroupCode0 using the “&” sign, and named this column GroupCode. After that, I wanted to give sequential numbers to the chart of accounts and concatenate it with the GroupCode. My goal is to generate a code that should unique for each Account in the Chart of Accounts. Whenever I add a new account to the chart of accounts, a unique code should be generated for it. In which the first digits (001) are indicating the AccountType, then there is a dash (-), then the next digits (001) are representing the account group, then there is a dash (-) and the last digits (0001) are representing each account of the charts of account.

For Example:
001-002-0001

In the example above:

001 is AccountType like “Asset”,

002 is AccountGroup like “Current Asset”,

0001 is an Account of Chart Of Account like “Cash in Hand”,

If you have any idea to do all of above things in a single field of my query to skip creating more than one fields and concatenate them, kindly share me the code.

Thank you

 

[arnelgp]

See query COACODE.

Cash In Hand has 1 as id, so it will have an act no: 001-001-0001

 

[Khurramshaikh47]

See query COACODE.

Cash In Hand has 1 as id, so it will have an act no: 001-001-0001

Thank you a lot dear anrel