No Zero?

[Perfac]

We have a Union query that Union all transactions. We use separate queries to return the totals for separate sections on the Balance sheet. Those are TOTAL QUERIES since they total all transactions against the specific accounts. When there is no transactions against that account, the total will be Null and not zero. When I use the NZ expression it still returns Null. I have used the NZ multiple times on queries that are not TOTAL queries and it worked. Is it because we work with TOTAL queries that it doesn't return 0?

[CJ_London]

Is it because we work with TOTAL queries that it doesn't return 0?

if there is nothing to total it won't return null or 0, there just won't be a row returned

Suspect you need to use a left join between the account and transactions table but depends on how you are doing things so you will need to copy and paste the sql to your query (not an image of the query grid).

[pbaldy]

Well, it isn't that there's a Null value in a record, there's no record at all. You'd need to join to a table of all accounts with the correct join specified. Then the Nz() function should work.

[Perfac]

Thanks. You are correct. To get to the BALANCE SHEET, first the all transaction Union query(will post the SQL in a minute) is put together by 13 selections. This all transactions query is then divided into many subqueries. Let's say in the query that returns Inventory for the previous period, there is no transactions it will return no record. Even if there are transactions in the Current period. The report shows nothing even for the current period since the two different periods end up in the same query. To prevent lagging or halting of the report we need to stay away from too many expressions. Our solution is to APPEND these queries to tables. Then use this tables on the BALANCE SHEET REPORT. The append queries append nothing when they find nothing of course. My thinking falls short to get a record in these tables that we append to. If I can get a record in there, my challenge is solved. Or any other ideas?

[Perfac]

SELECT q02Receipt.DocumentDate05, q02Receipt.DocNum07, q02Receipt.LedgerAccNum01, q02Receipt.CustomerName03, q02Receipt.CmbEnt_ID11 AS SubLdgAccNum01, q02Receipt.TransactionType01, q02Receipt.Debit10 AS Debit23, q02Receipt.Credit06 AS Credit21, [Debit23]-[Credit21] AS Balance03, q02Receipt.Check009
FROM q02Receipt
UNION ALL
SELECT q02ReceiptSub.DocumentDate05 AS [Date], q02ReceiptSub.DocNum08, q02ReceiptSub.LedgerAccNum04, q02ReceiptSub.CustomerName02, q02ReceiptSub.CmbEnt_ID12, q02ReceiptSub.TransactionType01, q02ReceiptSub.Debit07, q02ReceiptSub.Credit07, [Debit07]-[Credit07] AS Balance, q02ReceiptSub.Check009
FROM q02ReceiptSub
UNION ALL
SELECT q02Payment.DocumentDate04, q02Payment.DocNum11, q02Payment.LedgerAccNum01, q02Payment.SupplierName05, q02Payment.CmbEnt_ID08, q02Payment.TransactionType01, q02Payment.Debit05, q02Payment.Credit05, [Debit05]-[Credit05] AS Balance, q02Payment.Check009
FROM q02Payment
UNION ALL
SELECT q02PaymentSub.DocumentDate04, q02PaymentSub.DocNum12, q02PaymentSub.LedgerAccNum01, q02PaymentSub.SupplierName03, q02PaymentSub.CmbEnt_ID10, q02PaymentSub.TransactionType01, q02PaymentSub.Debit06, q02PaymentSub.Credit05, [Debit06]-[Credit05] AS Balance, q02PaymentSub.Check009
FROM q02PaymentSub
UNION ALL
SELECT q02InvSales.DocumentDate02, q02InvSales.DocNum05, q02InvSales.LedgerAccNum01, q02InvSales.CustomerName01, q02InvSales.CmbEnt_ID05, q02InvSales.TransactionType01, q02InvSales.Debit11, q02InvSales.Credit02, [Debit11]-[Credit02] AS Balance, q02InvSales.Check009
FROM q02InvSales
UNION ALL
SELECT q02InvSalesSub.DocumentDate02, q02InvSalesSub.DocNum06, q02InvSalesSub.LedgerAccNum05, q02InvSalesSub.CustomerName01, q02InvSalesSub.CmbEnt_ID55, q02InvSalesSub.TransactionType01, q02InvSalesSub.Debit03, q02InvSalesSub.ExclVat03, [Debit03]-[ExclVat03] AS Balance, q02InvSalesSub.Check009
FROM q02InvSalesSub
UNION ALL
SELECT q02InvSalesSub.DocumentDate02, q02InvSalesSub.DocNum06, 3600 AS LedgerAccNum01, q02InvSalesSub.CustomerName01, 63 AS VatSubLdgAccNo01, q02InvSalesSub.TransactionType01, q02InvSalesSub.Debit03, q02InvSalesSub.Vat08 AS Credit09, [Debit03]-[Credit09] AS Balance, q02InvSalesSub.Check009
FROM q02InvSalesSub
UNION ALL
SELECT q02InvPurchase.DocumentDate01, q02InvPurchase.DocNum03, t02LedgerAccount.LedgerAccNum01, q02InvPurchase.SupplierName01, q02InvPurchase.CmbEnt_ID03, q02InvPurchase.TransactionType01, q02InvPurchase.Debit01, q02InvPurchase.Credit01, [Debit01]-[Credit01] AS Balance, q02InvPurchase.Check009
FROM q02InvPurchase INNER JOIN t02LedgerAccount ON q02InvPurchase.LdgAcc_ID27 = t02LedgerAccount.LdgAccID
UNION ALL
SELECT q02InvPurchaseSub.DocumentDate07, q02InvPurchaseSub.DocNum04, q02InvPurchaseSub.LedgerAccNum01, q02InvPurchaseSub.SupplierName04, q02InvPurchaseSub.CmbEnt_ID62, q02InvPurchaseSub.SubLdgAccDesc07, q02InvPurchaseSub.ExclVat01 AS Debit12, q02InvPurchaseSub.Credit01, [Debit12]-[Credit01] AS Balance, q02InvPurchaseSub.Check009
FROM q02InvPurchaseSub
UNION ALL
SELECT q02InvPurchaseSub.DocumentDate07, q02InvPurchaseSub.DocNum04, 3600 AS VatLdgAccNo03, q02InvPurchaseSub.SupplierName04, 63 AS VatSubLdgAccNo02, q02InvPurchaseSub.SubLdgAccDesc07, q02InvPurchaseSub.Vat06 AS Debit13, q02InvPurchaseSub.Credit01, [Debit13]-[Credit01] AS Balance, q02InvPurchaseSub.Check009
FROM q02InvPurchaseSub
WHERE (((q02InvPurchaseSub.Vat06)>0))
UNION ALL
SELECT q02Journal.DocumentDate03, q02Journal.DocNum15, 0 AS LedgerAccNum11, t01TransactionType.TransactionType01, 0 AS CmbEnt_ID64, 0 AS SubLdgAccDesc09, 0 AS Debit33, 0 AS Credit30, [Debit33]-[Credit30] AS Balance06, q02Journal.Check009
FROM q02Journal INNER JOIN t01TransactionType ON q02Journal.TrnTpe_ID05 = t01TransactionType.TrnTpeID
UNION ALL
SELECT q02JournalSub.DocumentDate03, q02JournalSub.DocNum14, q02JournalSub.LedgerAccNum10, q02JournalSub.TransactionType01, q02JournalSub.CmbEnt_ID53, q02JournalSub.SubLdgAccDesc01, q02JournalSub.Debit04, q02JournalSub.Credit04, [Debit04]-[Credit04] AS Balance05, q02JournalSub.Check009
FROM q02JournalSub
UNION ALL SELECT q02JournalSub.DocumentDate03, q02JournalSub.DocNum14, 3600 AS LedgerAccNum01, q02JournalSub.TransactionType01, q02JournalSub.VatSubLdgAccNo03, q02JournalSub.EntityName01, q02JournalSub.VatDt01, q02JournalSub.VatCr01, [VatDt01]-[VatCr01] AS Balance07, q02JournalSub.Check009
FROM q02JournalSub
WHERE ((([VatDt01]-[VatCr01])>0));

[Perfac]

The following two queries; The first one sorts all transactions within a certain period for a certain group of accounts. The next one Totals the first one to get the total of all transactions in that period. It is this last query that returns no record, and messes up my balance sheet.

SELECT [UnBAllDocuments].DocumentDate05, [UnBAllDocuments].DocNum07, [UnBAllDocuments].LedgerAccNum01, [UnBAllDocuments].CustomerName03, [UnBAllDocuments].SubLdgAccNum01, [UnBAllDocuments].TransactionType01 AS EntityName, [UnBAllDocuments].Debit23, [UnBAllDocuments].Credit21, [UnBAllDocuments].Balance03
FROM UnBAllDocuments
WHERE ((([UnBAllDocuments].DocumentDate05)<=Forms!f07PrintFinancials!T003) And (([UnBAllDocuments].LedgerAccNum01)>=2000 And ([UnBAllDocuments].LedgerAccNum01)<3000));



SELECT [q0702MedTrmAstCur].LedgerAccNum01, Sum([q0702MedTrmAstCur].Balance03) AS CurrBalance02
FROM q0702MedTrmAstCur
GROUP BY [q0702MedTrmAstCur].LedgerAccNum01;

[pbaldy]

Try this with the appropriate table/field names

SELECT [q0702MedTrmAstCur].LedgerAccNum01, Sum(Nz([q0702MedTrmAstCur].Balance03, 0)) AS CurrBalance02
FROM q0702MedTrmAstCur RIGHT JOIN TableOfAccounts On [q0702MedTrmAstCur].LedgerAccNum01 = TableOfAccounts.AccountNumberField
GROUP BY [q0702MedTrmAstCur].LedgerAccNum01;

[Perfac]

Thank you!

[pbaldy]

They were not supposed to release that picture of me!!

[Perfac]

When I saw the zero's in that query I thought my challenge was solved. It is not. I then use an Append query to append that numbers to a new table. This step helps to prevent lagging or halting of the report. The Balance sheet report source the numbers from the tables. The question now is when I look at the append query in DESIGN it doesn't show to append the zero's. If I can manage to get a record in the "Append" table I believe it may do the job needed.

The append query SQL
INSERT INTO t0701LngTrmAst ( EntityName01, PrevBalance01, CurrBalance01 )
SELECT t01CombinedEntity.EntityName01, q0701LngTrmAstPrvTot.PrevBalance01, q0701LngTrmAstCurTot.CurrBalance01
FROM (q0701LngTrmAstCurTot INNER JOIN q0701LngTrmAstPrvTot ON q0701LngTrmAstCurTot.SubLdgAccNum01 = q0701LngTrmAstPrvTot.SubLdgAccNum01) INNER JOIN t01CombinedEntity ON q0701LngTrmAstPrvTot.SubLdgAccNum01 = t01CombinedEntity.CmbEntID;

[pbaldy]

Same basic idea applies, RIGHT or LEFT JOIN to the table containing account numbers.