Display queried records with Null values (null recordcount)

[Mishanya]

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?

 

[Ken Sheridan]

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 spreadsheet 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

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?

 

[Mishanya]

Hi Ken.
Thank You again for your practical replies.
I've never used an outer join before, always staying with the default Access
offer - now I've finally understood what is it for.
Querying tblTransactions and tblAccounts joined by outer join on AccountID
indeed does the job - in case of "blank" account I get AccountNumber with
blank TransactionQuantity. But when I add tblAssets in order to pull the
CurrentAssetPrice, I get
"The SQL statement could not be executed because it contains ambiguous outer
joins. To force one of the joins to be performed first, create a separate
query that performs the first join and then include that query in your SQL
statement. (Error 3258)"
I can't figure what the error means, but I guess Your 2nd suggestion
(subquery) would work. Or maybe, since AssetID from tblAssets is a foreign
key in tblTransactions, I need to tamper with tblAssets and tblTransactions'
join-type in the same manner (although I don't get the logic).

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 spreadsheet 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

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?

 

[Ken Sheridan]

The error is due to the fact that the joins in the query are asking
contradictory things of Access. What the message is suggesting is that you
include the transactions table in the query by means of an outer join, which
you've found works; then you join this query to the assets table in a new
query. It might work as a single query with an outer join between the assets
and transactions tables, but I think I'd need hands on your tables to see if
this is possible. The subquery solution should work, however, as should
using a computed control in the form, calling the DSum function to get the
aggregated value.

Ken Sheridan
Stafford, England

Hi Ken.
Thank You again for your practical replies.
I've never used an outer join before, always staying with the default Access
offer - now I've finally understood what is it for.
Querying tblTransactions and tblAccounts joined by outer join on AccountID
indeed does the job - in case of "blank" account I get AccountNumber with
blank TransactionQuantity. But when I add tblAssets in order to pull the
CurrentAssetPrice, I get
"The SQL statement could not be executed because it contains ambiguous outer
joins. To force one of the joins to be performed first, create a separate
query that performs the first join and then include that query in your SQL
statement. (Error 3258)"
I can't figure what the error means, but I guess Your 2nd suggestion
(subquery) would work. Or maybe, since AssetID from tblAssets is a foreign
key in tblTransactions, I need to tamper with tblAssets and tblTransactions'
join-type in the same manner (although I don't get the logic).

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 spreadsheet 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

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?

 

[Mishanya]

"...It might work as a single query with an outer join between the assets
and transactions tables..."

That's what I did. I had also to "outer-join" relationships of all other
tables related to Assets and Transactions tables in the query, such as
tblTransactionType (Buy/sell or 1/-1) and tblCurrency (wich normalizes the
sums of transactions by currency-rate so the account-balance will present
one-currency figure). It works now.

I've never met any profound tutorial on effects of tables-relationships'
join-types on SQL-statements executability (avoiding ambiguouty). I'll be
glad if You recommend some link on the issue.

Thank You again.

The error is due to the fact that the joins in the query are asking
contradictory things of Access. What the message is suggesting is that you
include the transactions table in the query by means of an outer join, which
you've found works; then you join this query to the assets table in a new
query. It might work as a single query with an outer join between the assets
and transactions tables, but I think I'd need hands on your tables to see if
this is possible. The subquery solution should work, however, as should
using a computed control in the form, calling the DSum function to get the
aggregated value.

Ken Sheridan
Stafford, England

Hi Ken.
Thank You again for your practical replies.
I've never used an outer join before, always staying with the default Access
offer - now I've finally understood what is it for.
Querying tblTransactions and tblAccounts joined by outer join on AccountID
indeed does the job - in case of "blank" account I get AccountNumber with
blank TransactionQuantity. But when I add tblAssets in order to pull the
CurrentAssetPrice, I get
"The SQL statement could not be executed because it contains ambiguous outer
joins. To force one of the joins to be performed first, create a separate
query that performs the first join and then include that query in your SQL
statement. (Error 3258)"
I can't figure what the error means, but I guess Your 2nd suggestion
(subquery) would work. Or maybe, since AssetID from tblAssets is a foreign
key in tblTransactions, I need to tamper with tblAssets and tblTransactions'
join-type in the same manner (although I don't get the logic).

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 spreadsheet 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

:

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?

 

[Ken Sheridan]

Google "sql join types explained" and you'll get plenty to choose from, e.g.
the following explains things fairly simply:


http://www.developertutorials.com/tutorials/mysql/sql-tutorial-2-050611/page1.html


It relates to mysql, but it’s the principles which matter.

Ken Sheridan
Stafford, England

"...It might work as a single query with an outer join between the assets
and transactions tables..."

That's what I did. I had also to "outer-join" relationships of all other
tables related to Assets and Transactions tables in the query, such as
tblTransactionType (Buy/sell or 1/-1) and tblCurrency (wich normalizes the
sums of transactions by currency-rate so the account-balance will present
one-currency figure). It works now.

I've never met any profound tutorial on effects of tables-relationships'
join-types on SQL-statements executability (avoiding ambiguouty). I'll be
glad if You recommend some link on the issue.

Thank You again.

The error is due to the fact that the joins in the query are asking
contradictory things of Access. What the message is suggesting is that you
include the transactions table in the query by means of an outer join, which
you've found works; then you join this query to the assets table in a new
query. It might work as a single query with an outer join between the assets
and transactions tables, but I think I'd need hands on your tables to see if
this is possible. The subquery solution should work, however, as should
using a computed control in the form, calling the DSum function to get the
aggregated value.

Ken Sheridan
Stafford, England

Hi Ken.
Thank You again for your practical replies.
I've never used an outer join before, always staying with the default Access
offer - now I've finally understood what is it for.
Querying tblTransactions and tblAccounts joined by outer join on AccountID
indeed does the job - in case of "blank" account I get AccountNumber with
blank TransactionQuantity. But when I add tblAssets in order to pull the
CurrentAssetPrice, I get
"The SQL statement could not be executed because it contains ambiguous outer
joins. To force one of the joins to be performed first, create a separate
query that performs the first join and then include that query in your SQL
statement. (Error 3258)"
I can't figure what the error means, but I guess Your 2nd suggestion
(subquery) would work. Or maybe, since AssetID from tblAssets is a foreign
key in tblTransactions, I need to tamper with tblAssets and tblTransactions'
join-type in the same manner (although I don't get the logic).


:

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 spreadsheet 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

:

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?

 

[Mishanya]

great!
Thanks a lot!

Misha.

Google "sql join types explained" and you'll get plenty to choose from, e.g.
the following explains things fairly simply:


http://www.developertutorials.com/tutorials/mysql/sql-tutorial-2-050611/page1.html


It relates to mysql, but it’s the principles which matter.

Ken Sheridan
Stafford, England

"...It might work as a single query with an outer join between the assets
and transactions tables..."

That's what I did. I had also to "outer-join" relationships of all other
tables related to Assets and Transactions tables in the query, such as
tblTransactionType (Buy/sell or 1/-1) and tblCurrency (wich normalizes the
sums of transactions by currency-rate so the account-balance will present
one-currency figure). It works now.

I've never met any profound tutorial on effects of tables-relationships'
join-types on SQL-statements executability (avoiding ambiguouty). I'll be
glad if You recommend some link on the issue.

Thank You again.

The error is due to the fact that the joins in the query are asking
contradictory things of Access. What the message is suggesting is that you
include the transactions table in the query by means of an outer join, which
you've found works; then you join this query to the assets table in a new
query. It might work as a single query with an outer join between the assets
and transactions tables, but I think I'd need hands on your tables to see if
this is possible. The subquery solution should work, however, as should
using a computed control in the form, calling the DSum function to get the
aggregated value.

Ken Sheridan
Stafford, England

:

Hi Ken.
Thank You again for your practical replies.
I've never used an outer join before, always staying with the default Access
offer - now I've finally understood what is it for.
Querying tblTransactions and tblAccounts joined by outer join on AccountID
indeed does the job - in case of "blank" account I get AccountNumber with
blank TransactionQuantity. But when I add tblAssets in order to pull the
CurrentAssetPrice, I get
"The SQL statement could not be executed because it contains ambiguous outer
joins. To force one of the joins to be performed first, create a separate
query that performs the first join and then include that query in your SQL
statement. (Error 3258)"
I can't figure what the error means, but I guess Your 2nd suggestion
(subquery) would work. Or maybe, since AssetID from tblAssets is a foreign
key in tblTransactions, I need to tamper with tblAssets and tblTransactions'
join-type in the same manner (although I don't get the logic).


:

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 spreadsheet 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

:

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?