If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Display queried records with Null values (null recordcount)
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? |
#2
|
|||
|
|||
Display queried records with Null values (null recordcount)
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*CurrentAssetPr ice) 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*CurrentAssetPr ice) 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*CurrentA ssetPrice", "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? |
#3
|
|||
|
|||
Display queried records with Null values (null recordcount)
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). "Ken Sheridan" wrote: 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*CurrentAssetPr ice) 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*CurrentAssetPr ice) 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*CurrentA ssetPrice", "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? |
#4
|
|||
|
|||
Display queried records with Null values (null recordcount)
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 "Mishanya" wrote: 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). "Ken Sheridan" wrote: 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*CurrentAssetPr ice) 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*CurrentAssetPr ice) 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*CurrentA ssetPrice", "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? |
#5
|
|||
|
|||
Display queried records with Null values (null recordcount)
"...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. "Ken Sheridan" wrote: 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 "Mishanya" wrote: 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). "Ken Sheridan" wrote: 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*CurrentAssetPr ice) 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*CurrentAssetPr ice) 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*CurrentA ssetPrice", "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? |
#6
|
|||
|
|||
Display queried records with Null values (null recordcount)
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/tu...611/page1.html It relates to mysql, but it’s the principles which matter. Ken Sheridan Stafford, England "Mishanya" wrote: "...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. "Ken Sheridan" wrote: 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 "Mishanya" wrote: 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). "Ken Sheridan" wrote: 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*CurrentAssetPr ice) 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*CurrentAssetPr ice) 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*CurrentA ssetPrice", "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? |
#7
|
|||
|
|||
Display queried records with Null values (null recordcount)
great!
Thanks a lot! Misha. "Ken Sheridan" wrote: 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/tu...611/page1.html It relates to mysql, but it’s the principles which matter. Ken Sheridan Stafford, England "Mishanya" wrote: "...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. "Ken Sheridan" wrote: 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 "Mishanya" wrote: 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). "Ken Sheridan" wrote: 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*CurrentAssetPr ice) 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*CurrentAssetPr ice) 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*CurrentA ssetPrice", "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? |
Thread Tools | |
Display Modes | |
|
|