Showing Items with no values--Simple Question I hope

  • Thread starter Thread starter hongloumeng
  • Start date Start date
H

hongloumeng

I need to make a query (ultimately a pivot chart) that shows all the
departments in my place of work. The query must also show whether they did or
did not submit a report.

If they submitted a report, I have the data. I can run a query that shows
all the departments that submitted data.

But I need the query to also show the departments that did not submit data.

How should I go about this? I'm sure this has been covered before, please
feel free to direct me to a stable resource if that would be easier.

Thanks.
 
I need to make a query (ultimately a pivot chart) that shows all the
departments in my place of work. The query must also show whether they did or
did not submit a report.

If they submitted a report, I have the data. I can run a query that shows
all the departments that submitted data.

But I need the query to also show the departments that did not submit data.

How should I go about this? I'm sure this has been covered before, please
feel free to direct me to a stable resource if that would be easier.

Thanks.
Click to expand...

You don't describe your table structures, which makes it a bit hard to be
specific... but you can (I hope) create a Query joining the Departments toble
to the table of reports. Select the join line in the query definition window
and change the join type to a "left outer join" by choosing option 2 (or 3):
"Show all data in Departments and matching data in Reports".
 
You should have a table of departments.

First Query: Get all departments that have a report (presumably in a specified
period of time or for a specified report or a combination of both)

Second Query: The table of departments and the above query joined together on
the department id. Double-click the join line and choose show all departments
table and only matching query records.



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Thank you both. This works quite well with one exception:

in the pivot chart the departments without any data do not show up. In
datasheet view I see all the departments regardless of whether they have data
or not, but once I make a chart I only see the ones with data.

Any suggestions?
 
I've just realized that when I choose show all months 2007 through 2008, the
empty departments show up fine. But if I narryow the display to just 2008 the
empty departments go away.

Any sugggestions?
 
I've just realized that when I choose show all months 2007 through 2008, the
empty departments show up fine. But if I narryow the display to just 2008 the
empty departments go away.
Click to expand...

Please post the SQL view of your query. You may need an "OR IS NULL" criterion
to pick up the missing departments.
 
Thank you.

SELECT [Unit Names].[Unit Name], [qryLocation Query].Month, [qryLocation
Query].[Percentage Compliant]
FROM [Unit Names] LEFT JOIN [qryLocation Query] ON [Unit Names].[Unit Name]
= [qryLocation Query].[Unit Change];
 
Thank you.

SELECT [Unit Names].[Unit Name], [qryLocation Query].Month, [qryLocation
Query].[Percentage Compliant]
FROM [Unit Names] LEFT JOIN [qryLocation Query] ON [Unit Names].[Unit Name]
= [qryLocation Query].[Unit Change];



Click to expand...

There's nothing in this query to restrict the display to just 2008. How are
you adding that constraint? What you've posted is a query referencing a field
named Month; be aware that Month is a reserved word, for the builtin Month()
function, and a bad choice as a fieldname! What is the datatype of Month?
Where in your table is the year specified? Do you have a date/time field
underlying these values?
 
Back
Top