Ms access table merge

[njain8]

Table1
Acct Numb Account Type Asset Mkt Val Acct Asset MV
881009201 Main Account 30 120
881009201 Main Account 40 120
881009201 Main Account 20 120
881009201 Main Account 30 120
881009203 Sub Account 50 80
881009203 Sub Account 10 80
881009203 Sub Account 20 80
Table2
Acct Numb Account Type Asset Mkt Val Acct Asset MV
881009201 Main Account 30 200
881009201 Main Account 40 200
881009201 Main Account 20 200
881009201 Main Account 30 200
881009201 Sub Account 80 200


I am Having a Table1 with the information given above. I am having 2 types of account:
1. Main Account (ending with 01)
2. Sub Account (ending with 03)
Now, I need a table with Having All the Main Account. But All the Sub Account Club into a Single Account No. Whose Account no. will be same as of its Main Account No. but the Account Type wii be [Sub Account].
Now we have 2 more colums:
1. Asset Mkt Val
2. Acct Asset MV
In Table 1: The [Acct Asset MV] shows the sum of Main Account ( 30+ 40+ 20+ 30= 120) and [Asset Mkt Val] shows the Sum of Sub Accont (50+10+20 = 80).
Now in Table 2: I want to have [Acct Asset MV] should be some of all account =(120+80= 200) and in Sub Account the [Asset Mkt Val] should be 80.

[June7]

Are you saying that 881009203 is a subaccount for 881009201? How is query to know that? Do you have a table that defines these associations? Are all accounts ending in 01 main accounts? Everything else is a subaccount?

In a query, maybe create a field with calculation for the main account

SELECT *, Left([Acct Numb],7) & "01" AS MainNum FROM tablename;

Now use that query as datasource for aggregate (Totals) query or a report.

[njain8]

Are you saying that 881009203 is a subaccount for 881009201? How is query to know that? Do you have a table that defines these associations? Are all accounts ending in 01 main accounts? Everything else is a subaccount?

In a query, maybe create a field with calculation for the main account

SELECT *, Left([Acct Numb],7) & "01" AS MainNum FROM tablename;

Now use that query as datasource for aggregate (Totals) query or a report.

I Know that the account ending with 03 are sub account. I have to create a table which is having:
1. All Main Account
2. Sub Account ending with 03 replaces with the 01(last 2 digits) and Showing the aggregate for Mkt_Val and Asset_val.

[June7]

Did you try suggestion?