Chart Labelling Problem

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi Everyone!

I'm creating a chart based a table. This table contain data about where
students use computer. It can be one of 3 values: Home, School, or Both. The
column is in "text" data format.

However, this table only contains records with Home and Work as data entries
and no records that contain "Both". When I created the chart, the chart only
show Home and School on X axis. "Both" is missing.

i.e. I need the graph to show 25 on Home, 70 on School, 0 on Both. But
Access is not showing it.

Following is the SQL generated for my chart

SELECT StudentData.UseOfComputer, Count(*) AS [Students] FROM StudentData
WHERE StudentData.Classroom="A"
GROUP BY StudentData.UseOfComputer
ORDER BY StudentData.UseOfComputer;

Please help me out!

Thank you!

Mike

P.S. I'm using Access 2003
 
Mike:

The problem is in your underlying query. If there are no "Boths" that are
returned in the group by, then Graph won't display the "Both" option.

To solve this, you need to combine your existing query with another that
returns Home, School and Both with a left join to the exisitng query. If
you don't have a table that contains those values, then create it (setting
the field name to [UseOfComputer] for simplicity) and add it to the new
query and then add your existing query, creating an outer join between the
field where you've got all the locations and the [UseOfComputer] field of
the exiting query, including all records from the table which contains all
three values and only those records that are equal from your existing query.
Display in the resulting query the [UseofComputer] field from the table and
Alias the [Students] field like this: StudentsUsing: IIF(Is
Null([Students]),0,[Students])

That should do it
 
Back
Top