my self avi developing accounting application in VB6 and Access for traders. i have 2 tables AccountTable And Accounthead Table Fields r
AccountTable - Accountcode Double [AutoIncremented i.e max value of in table]
Accountname String
Accounthead - PrimaryGroup Double
Subgroup Double
Ledgername Double
OpeningBal Currency
Address String
Phone String
if i add any customer record say
Name : Sachin Tendulkar
Address:Mumbai
OpeningBal:25000.00
Phone - 919890155910 then this record will save in both table as follows
AccountTable - Accountname ---Sachin Tedulkar
Accountcode-----100
Accounthead- PrimaryGroup--1
Subgroup --6
Ledgername---100
Address ---Mumbai
OpeningBal-25000.00
Phone-----919890155910
now u will think what is primarygroup and subgroup they r accountcodes of my predefined accounts like assets-1 liabilities-2 income-3 and sundry debtors-6 so on. these accountname and codes r stores in both table.now my problem is i had more than 2000 records in both tables. iwant to show all records in report format i.e
Name Address OpeningBal Subgroup
Sachin Tendulkar Mumbai 25000 sundrydebtors
my all names r stored in my accountTable and if i want to find name of my ledgername 100 and its subgroup name also i cant relate ledgername and subgroup with accountcode in 1 query my query is
SELECT AC.AccountName,(select AccountName from AccountTable as ACC where ACC.AccountCode = AH.Subgroup) AS subgrp,AccountCode,openingbal,address,Phone
FROM AccountTable AS AC, AccountHead AS AH
this query runs properly but becaz of my 2000 records it takes to much time to execute so plz help me in writting proper query
AccountTable - Accountcode Double [AutoIncremented i.e max value of in table]
Accountname String
Accounthead - PrimaryGroup Double
Subgroup Double
Ledgername Double
OpeningBal Currency
Address String
Phone String
if i add any customer record say
Name : Sachin Tendulkar
Address:Mumbai
OpeningBal:25000.00
Phone - 919890155910 then this record will save in both table as follows
AccountTable - Accountname ---Sachin Tedulkar
Accountcode-----100
Accounthead- PrimaryGroup--1
Subgroup --6
Ledgername---100
Address ---Mumbai
OpeningBal-25000.00
Phone-----919890155910
now u will think what is primarygroup and subgroup they r accountcodes of my predefined accounts like assets-1 liabilities-2 income-3 and sundry debtors-6 so on. these accountname and codes r stores in both table.now my problem is i had more than 2000 records in both tables. iwant to show all records in report format i.e
Name Address OpeningBal Subgroup
Sachin Tendulkar Mumbai 25000 sundrydebtors
my all names r stored in my accountTable and if i want to find name of my ledgername 100 and its subgroup name also i cant relate ledgername and subgroup with accountcode in 1 query my query is
SELECT AC.AccountName,(select AccountName from AccountTable as ACC where ACC.AccountCode = AH.Subgroup) AS subgrp,AccountCode,openingbal,address,Phone
FROM AccountTable AS AC, AccountHead AS AH
this query runs properly but becaz of my 2000 records it takes to much time to execute so plz help me in writting proper query