help on joining two table

  • Thread starter Thread starter Aivars
  • Start date Start date
A

Aivars

Hello, group
I need to extract all the records in first table (its chart of accounts table (476 records) and from a.table (main ledger) where all booking entries are stored. The SQL statement below works by extracting only 370 entries which means those accountnos from first table which are not in the second table are not shown. But i need to show all records from chart_acc (b.table) and if there is no entry in a. table show nothing or 0.
SQL:
select b.accountno, b.account_name ,sum(a.amount) from sysadm_chartacc as b left join sysadm_accountdetails as a on a.accountno=b.accountno where a.financial_year=2004
group by b.accountno, b.account_name

Level: beginner
Access 2003

Thanks
Aivars
 
Hello, group
I need to extract all the records in first table (its chart of accounts table (476 records) and from a.table (main ledger) where all booking entries are stored. The SQL statement below works by extracting only 370 entries which means those accountnos from first table which are not in the second table are not shown. But i need to show all records from chart_acc (b.table) and if there is no entry in a. table show nothing or 0.
SQL:
select b.accountno, b.account_name ,sum(a.amount) from sysadm_chartacc as b left join sysadm_accountdetails as a on a.accountno=b.accountno where a.financial_year=2004
group by b.accountno, b.account_name
Click to expand...


If there is no record in a for a given account, then the WHERE
a.financial_year = 2004 criterion will certainly NOT be true!

Change it to

where a.financial_year=2004 or a.financial_year IS NULL

John W. Vinson[MVP]
 
Thanks for your effort, John,
Now after using an OR expression the query retrieves 437 records and still some accounts (23) (=467-437) from the chart of accounts table are not included in resulting set. What else could be wrong?
Thank you very much
Aivars
Hello, group
I need to extract all the records in first table (its chart of accounts table (476 records) and from a.table (main ledger) where all booking entries are stored. The SQL statement below works by extracting only 370 entries which means those accountnos from first table which are not in the second table are not shown. But i need to show all records from chart_acc (b.table) and if there is no entry in a. table show nothing or 0.
SQL:
select b.accountno, b.account_name ,sum(a.amount) from sysadm_chartacc as b left join sysadm_accountdetails as a on a.accountno=b.accountno where a.financial_year=2004
group by b.accountno, b.account_name
Click to expand...


If there is no record in a for a given account, then the WHERE
a.financial_year = 2004 criterion will certainly NOT be true!

Change it to

where a.financial_year=2004 or a.financial_year IS NULL

John W. Vinson[MVP]
 
Thanks for your effort, John,
Now after using an OR expression the query retrieves 437 records and still some accounts (23) (=467-437) from the chart of accounts table are not included in resulting set. What else could be wrong?
Thank you very much
Aivars
Click to expand...

I'll bet the missing 23 accounts have records in the Details table -
but they're not in 2004. Therefore the AccountYear is neither null,
nor equal to 2004; so the records aren't retrieved.

You'll probably need to do this in two queries: first, create a Totals
query on AccountDetails, with the 2004 criterion and summing the
amounts; then create a second query joining the accounts table to this
Query with a left join.

John W. Vinson[MVP]
 
Back
Top