RE: Display queried records with Null values (null recordcount)

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

To include the transactions table in the query you'd join it to the accounts
table using an OUTER JOIN. The query would return multiple rows per account,
however, one for each transaction, and you'd consequently have multiple
records per transaction in the form, which you probably don't want. You
could group the query on all its existing columns and SUM the expression in
the query. I'm assuming you also need to include the tblAssets table to get
the CurrentAssetPrice:

SELECT <existing column list>
SUM(TransactionQuantity*CreditDebit*CurrentAssetPrice) AS AccountBalance
FROM <remainder of existing query with tblTansactions and tblAssets included
using an outer join>
GROUP BY <existing column list>;

or an alternative solution would be, rather than join the
transactions/assets tables to the accounts table, to compute the account
balance by means of a subquery in the current query's SELECT clause:

SELECT <existing column list>,
(SELECT SUM(TransactionQuantity*CreditDebit*CurrentAssetPrice)
FROM tblTransactions INNER JOIN tblAssets
ON tblTransactions.AssetID = tblAssets.AssetID
WHERE tblTransactions.AccountID = tblAccounts.AccountID) AS
AccountBalance
FROM <remainder of existing query>;

In the form the ControlSource property of the text box would then be:

=Nz(AccountBalance,0)

However you can do it without including the transactions table in the query
at all by calling the DSum function in the expression (not formula BTW,
that's a spread*** concept) as the text box's ControlSource property. The
DSUM function would sum the values from a query which joins the transactions
and assets tables, so you'll need to create that first:

=Nz(DSum("TransactionQuantity*CreditDebit*CurrentAssetPrice",
"YourQueryNameGoesHere", "AccountID = " & [AccountID"),0)

Note the above is all one line; it will have been split in your newsreader.
If the query is currently updatable the last solution will keep it so,
whereas the earlier ones will make it, and consequently the form, read only.

Ken Sheridan
Stafford, England

"Mishanya" wrote:

I have frmListOfAccounts showing AccountName, AccountNumber, BankName and
BranchName. It's based on query wich in turn is based on tblAccounts,
tblBanks and tblBranches.

tblTransactions tracks accounts' transactions: AccountID (from tblAccounts),
AssetID (from tblAssets), CreditDebit(1/-1), TransactionQuantity,
AssetTransactionPrice.

I want to add calculated field AccountBalance to frmListOfAccounts. Its
formula is:

Sum([TransactionQuantity]*[CreditDebit]*[CurrentAssetPrice])

but when I add tblTransactions to the form underlying query, the outcome
naturally eliminates the records of accounts which do not have any
transactions yet (they do show, though, if its AccountBalance is 0, i.e the
account had had transactions but it's Credit/Debit balance now is 0).

Still, I want to see the list of all accounts and its balances, as the form
responsible for entering transactions is accessed from frmListOfAccounts.

What approach should I use, so that
Sum([TransactionQuantity]*[CreditDebit]*[CurrentAssetPrice]) would return 0
instead of NULL in the query when there are no transactions' records for a
given account?

.

Quantcast