Displaying Categories with Non-zero values

[Dez]

I hope I can explain this clearly. I have a table that shows budget, current
month cost, and year-to-date cost for certain cost categories. The budgets
and cost totals pull from two different tables.

The problem is that when I run this report, the categories only show on the
report that have costs (either current month or YTD). If it has a budget but
no cost, that category will not show on the report.

I've tried entering "<>0" in certain columns, but to no avail. Any ideas?

Thanks,
Dez

 

[mscertified]

The first thing to determine is if you have the data in your report record
source. In design view for the report click View--Properties--Data--Record
source.
You will then need to go to that query and post the SQL so we can look at
it. I'm assuming the record source is a query because you say the data comes
from two tables.

-Dorian

 

[Dez]

Is this what you're referring to?

SELECT [Subtotal Data].[Cost Center], [Group Names by Account].Description,
[Subtotal Data].[Cost Category], [OB Budgets by Categ].Budget, [Subtotal
Data].[Current Month] AS [Current Month Cost], [Subtotal Data].[Year-to-Date]
AS [Year-to-Date Cost], [Year-to-Date Cost]/[Budget] AS [Percent Spent]
FROM ([Subtotal Data] LEFT JOIN [OB Budgets by Categ] ON ([Subtotal
Data].[Cost Category] = [OB Budgets by Categ].[Cost Category]) AND ([Subtotal
Data].[Cost Center] = [OB Budgets by Categ].[OB Account])) LEFT JOIN [Group
Names by Account] ON [Subtotal Data].[Cost Center] = [Group Names by
Account].[Cost Center]
WHERE ((([OB Budgets by Categ].Budget)<>0)) OR ((([Subtotal Data].[Current
Month])<>0)) OR ((([Subtotal Data].[Year-to-Date])<>0));

 

[Evi]

Try this filter for the columns.

<>0 OR Is Not Null

Note, you must not have quote marks around the <>0 if the field is a number
field.
Evi

 

[Dez]

Thanks but that didn't get it either.

Try this filter for the columns.

<>0 OR Is Not Null

Note, you must not have quote marks around the <>0 if the field is a number
field.
Evi

 

[John Spencer]

SELECT [Subtotal Data].[Cost Center]
, [Group Names by Account].Description
, [Subtotal Data].[Cost Category]
, [OB Budgets by Categ].Budget
, [Subtotal Data].[Current Month] AS [Current Month Cost]
, [Subtotal Data].[Year-to-Date] AS [Year-to-Date Cost]
, [Year-to-Date Cost]/[Budget] AS [Percent Spent]
FROM ([Subtotal Data] LEFT JOIN [OB Budgets by Categ]
ON ([Subtotal Data].[Cost Category] = [OB Budgets by Categ].[Cost Category])
AND ([Subtotal Data].[Cost Center] = [OB Budgets by Categ].[OB Account]))
LEFT JOIN [Group Names by Account]
ON [Subtotal Data].[Cost Center] = [Group Names by Account].[Cost Center]
WHERE ((([OB Budgets by Categ].Budget)<>0))
OR ((([Subtotal Data].[Current Month])<>0))
OR ((([Subtotal Data].[Year-to-Date])<>0));

Your where clause is overiding the left joins ability to give you all the
records. You can try changing it to
WHERE [OB Budgets by Categ].Budget<>0
OR [Subtotal Data].[Current Month]<>0
OR [Subtotal Data].[Current Month] is Null
OR [Subtotal Data].[Year-to-Date]<>0
OR [Subtotal Data].[Year-to-Date] is Null

Another problem is that probably want to use a RIGHT JOIN in the above and not
a LEFT JOIN. The LEFT JOIN (without criteria) would return all records in
SubTotal data and only matching records in Group Names by Account. I think
you want the other way round.

Or you can try dropping all the criteria on SubTotal Data table.

Or you are going to need to use nested queries - where you get the information
from SubTotal Data in a separate query and then use the separate query in
place of the table in the above query.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Is this what you're referring to?

SELECT [Subtotal Data].[Cost Center], [Group Names by Account].Description,
[Subtotal Data].[Cost Category], [OB Budgets by Categ].Budget, [Subtotal
Data].[Current Month] AS [Current Month Cost], [Subtotal Data].[Year-to-Date]
AS [Year-to-Date Cost], [Year-to-Date Cost]/[Budget] AS [Percent Spent]
FROM ([Subtotal Data] LEFT JOIN [OB Budgets by Categ] ON ([Subtotal
Data].[Cost Category] = [OB Budgets by Categ].[Cost Category]) AND ([Subtotal
Data].[Cost Center] = [OB Budgets by Categ].[OB Account])) LEFT JOIN [Group
Names by Account] ON [Subtotal Data].[Cost Center] = [Group Names by
Account].[Cost Center]
WHERE ((([OB Budgets by Categ].Budget)<>0)) OR ((([Subtotal Data].[Current
Month])<>0)) OR ((([Subtotal Data].[Year-to-Date])<>0));

The first thing to determine is if you have the data in your report record
source. In design view for the report click View--Properties--Data--Record
source.
You will then need to go to that query and post the SQL so we can look at
it. I'm assuming the record source is a query because you say the data comes
from two tables.

-Dorian

 

[Evi]

I don't think I understood you properly Dez.

See if this is true:
TblBudget contains all the Budgets.
Not every Budget is mentioned in linked table, TblCosts

You want to see all the Budgets even if they aren't in TblCosts

If the answer to this is yes then
Double click the join between the tables in the Query grid and change the
option to Show All Records from TblBudget

You then have to decide what to do about the Nulls which you will see. There
are several options available but if you do any arithmetic on the figures,
you will get unpredictable results until you decide what you want to do.
Evi