Showing Items with no values--Simple Question I hope
in [Getting Started]
|
Prev: starting up
Next: win2pdf mail helper loop problem
From: hongloumeng on 17 Jun 2008 19:30 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.
From: John W. Vinson on 17 Jun 2008 20:05 On Tue, 17 Jun 2008 16:30:01 -0700, hongloumeng <hongloumeng(a)discussions.microsoft.com> wrote: >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. 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". -- John W. Vinson [MVP]
From: John Spencer on 18 Jun 2008 07:11 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 hongloumeng wrote: > 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.
From: hongloumeng on 18 Jun 2008 18:43 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? "John Spencer" wrote: > 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 > > hongloumeng wrote: > > 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. >
From: hongloumeng on 18 Jun 2008 19:20
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? "hongloumeng" wrote: > 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? > > "John Spencer" wrote: > > > 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 > > > > hongloumeng wrote: > > > 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. > > |