Access Query to SQL 7.0

[BlackKnight]

Hi folks,

I have an Access query (qryCOA) containing a table left joined to another Access query (qryAccountBalance). I want to convert qryCOA into SQL 7. How do I do it? Thanx in advance. The following is the SQL of the 2 Access queries:

qryCOA:

SELECT tblChartOfAccounts.ID, tblChartOfAccounts.lngsort, tblChartOfAccounts.strAccountID, tblChartOfAccounts.lngLevel, tblChartOfAccounts.strDescription, tblChartOfAccounts.ysnINActive, tblChartOfAccounts.strAccountType, tblChartOfAccounts.StrAccountCatagory, tblChartOfAccounts.strOrgAccountID, tblChartOfAccounts.curOpeningBalance, First(CCur(IA_NullToZero([bal]))) AS curBalance, tblChartOfAccounts.ysnHeader
FROM tblChartOfAccounts LEFT JOIN qryAccountBalance ON tblChartOfAccounts.ID = qryAccountBalance.lngAccountID
GROUP BY tblChartOfAccounts.ID, tblChartOfAccounts.lngsort, tblChartOfAccounts.strAccountID, tblChartOfAccounts.lngLevel, tblChartOfAccounts.strDescription, tblChartOfAccounts.ysnINActive, tblChartOfAccounts.strAccountType, tblChartOfAccounts.StrAccountCatagory, tblChartOfAccounts.strOrgAccountID, tblChartOfAccounts.curOpeningBalance, tblChartOfAccounts.ysnHeader
ORDER BY tblChartOfAccounts.lngsort, tblChartOfAccounts.strAccountID;
======================================

qryAccountBalance:

SELECT tblGeneralJournalDetail.lngAccountID, Sum(isnull([dblDebit],0)) AS debit, Sum(isnull([dblCredit],0)) AS Credit, isnull([debit],0)-isnull([credit],0) AS Bal FROM tblGeneralJournalDetail GROUP BY tblGeneralJournalDetail.lngAccountID;

Have a good one!
Keith

 

[TomSark]

I don't know access, but what is: First(CCur(IA_NullToZero([bal]))) ?

Tom

 

[BlackKnight]

Hi Tom,

The line, "First(CCur(IA_NullToZero([bal])))" can be changed to: isnull([bal], 0)

The rest of the query is the same.

Thanx again.

Have a good one!
Keith