SELECT 
 GA2.[ACCOUNTNUMBER] "DR Account number",
GA2.[DESCRIPTION] "DR Account description", TD.CLASS "Class Number",
INVD.[AMOUNT] "Amount",
(SELECT GA1.ACCOUNTNUMBER FROM GL7ACCOUNTS GA1 WHERE GA1.DESCRIPTION = 'Accounts Payable' AND GA1.STATUS = 1 AND GA1.CLASS = GA2.CLASS AND GA1.GL7FUNDSID = GA2.GL7FUNDSID) AS 'CR Account Number', 
 'Accounts Payable' AS 'CR Account Description', 
 PROJ.[PROJECTID] "Project ID",
PROJ.[DESCRIPTION] "Project Description",
TE.DESCRIPTION "Class"

FROM 
DBO.AP7INVOICES AS INV 
INNER JOIN DBO.AP7VENDORS AS AP7INVOICES_AP7VENDORS ON INV.AP7VENDORSID = AP7INVOICES_AP7VENDORS.AP7VENDORSID 
LEFT OUTER JOIN DBO.AP7INVOICEDISTRIBUTION AS INVD ON INV.AP7INVOICESID = INVD.PARENTID 
LEFT OUTER JOIN DBO.GL7ACCOUNTS AS GA2 ON INVD.GL7ACCOUNTSID = GA2.GL7ACCOUNTSID 
LEFT OUTER JOIN DBO.BBTRANSACTIONDISTRIBUTIONS AS TD ON INVD.BBDISTRIBUTIONSID = TD.BBDISTRIBUTIONSID 
LEFT OUTER JOIN DBO.BBDISTRIBUTIONS AS BBD on TD.BBDISTRIBUTIONSID = BBD.BBDISTRIBUTIONSID
LEFT OUTER JOIN DBO.GL7PROJECTS AS PROJ ON TD.GL7PROJECTSID = PROJ.GL7PROJECTSID 
LEFT OUTER JOIN DBO.TABLEENTRIES AS TE ON TD.CLASS = TE.TABLEENTRIESID

WHERE 
AP7INVOICES_AP7VENDORS.[USERDEFINEDID]  =  '1002' -- show invoice for a specific vendor
AND BBD.MISCENTRY <> -1 -- show Distribtion only and not Miscellaneous 
and INV.INVOICENUMBER = '121212' -- show specific invoice number
AND PROJ.PROJECTID IS NOT NULL -- filter for valid project ids

Disclaimer: We provide programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes you are familiar with Structured Query Language and the tools used to create and modify SQL statements and Crystal Reports. Our Customer Support may help explain the functionality of a particular procedure, but we will not modify, or assist you with modifying, these examples to provide additional functionality.