Show a group Item with 0 sum in totals query (1 Viewer)

[AJR]

Hi

Thanks for taking the time to check out my question

What I need to know is if I can somehow get a Totals Query to include a line when it returns a null value.

A very simplified explanation of the situation:

I have a database that generates a report showing different categories of Stock and the total dollar amount in each category.

I have attached a database that replicates my question


To accomplish this I have two tables:

1) Stocks- Contains all the stocks the user may want to own. This table has three fields: StockName (Key), Category, and Share Price.

2) Holdings- Contains all the stocks they actually do own. This table has three
fields: I.D. (Key), StockName and #Shares.

(There is a one to many relationship between [Stocks]![StockName] (One) and [Holdings]![StockName] (Many). The reason for the one to many is that in the actual database there are different accounts so [Holdings] could contain the same stock multiple times if it existed in multiple accounts.)

The record source for my report is a query, which is where the problem occurs.

The query is a totals query with two columns:

1) Field: Category
Table: Stocks
Total: Group By

2) Field: CategoryTotal$:Sum([Holdings]![#Shares]*[Stocks]![SharePrice])
Table:
Total: Expression


So the query returns one line for each Category of Stock and the total amount of that type held (Sum of # of Shares * Share Value).

The problem arises when a category exists but has no stock in the Holdings table that has that category assigned to it. That category will not be shown in the results of the query.

Ideally I would like it to show with a CategoryTotal$ of $0.00. Then it would also show on my report as $0.00 and the user would realize it existed.

Can I get the total query to return a category when [Holdings]![#Shares]*[Stocks]!
[SharePrice] = 0?



Thanks again for your time and patience. I am very new to this and have only just touched on VBA but if it is required I'll figure it out.

 

[ypma]

Have you tried using the Nz function ?

Nz([itemOne]) + Nz([ItemTwo])

One of this functions use is to change blanks to Zero So try it out

Regards

 

[plog]

You need to make a LEFT JOIN from tblStocks to tblHoldings. This will force all stocks to show up. Then, since you are doing a calculation, you need to use the NZ function (http://www.techonthenet.com/access/functions/advanced/nz.php) around that calculation to force it to 0 if the calculation contains null values.

This is the SQL you should use:

SELECT tblStocks.Category, Sum(Nz([tblHoldings]![#shares]*[tblStocks]![SharePrice],0)) AS [TypeTotal$]
FROM tblStocks LEFT JOIN tblHoldings ON tblStocks.[StockName] = tblHoldings.[StockName]
GROUP BY tblStocks.Category;