Building graph on query?

[rc-alex]

Hello,

I am trying to insert graphs based on a query into a report, but doesn't seem to be working. Here is my setup:

Report:

Year:2010
DepartmentA
Clerk1 - graph all history for [DeparmtentID=A, ClerkID=1]
Clerk3 - graph all history for [DeparmtentID=A, ClerkID=3]

DepartmentB
Clerk1 - graph all history for [DeparmtentID=B, ClerkID=1]
Clerk5 - graph all history for [DeparmtentID=B, ClerkID=5]
Clerk7 - graph all history for [DeparmtentID=B, ClerkID=7]

The clerks display in the approprate areas (even clerk 1 who is in two departments).

Here is the query I am trying to use.

Year...DepartmentID...ClerkID...Sales
2010...A...................1...........5,000
2009...A...................1...........4,000
2008...A...................1...........3,000
2010...A...................3...........6,000
2009...A...................3...........4,500
2010...B...................1...........3,700
2009...B...................1...........4,300
(continued...)

The DeptA, Clerk1 graph should have 3 bars= 5,000, 4,000, and 3,000.
The DeptA, Clerk2 graph should have 2 bars= 6,000 and 4,500
The DeptB, Clerk1 (same clerk1) should have 3,700 and 4,300.

However, it keeps wanting to display the count of the Sales rather than the amount for each year. Each year should be a separate bar, with all values matching both that Department and Clerk in that section of the report.

Am I doing something wrong?

Thanks!

 

[Guus2005]

create a crosstab query and when you rightclick on count of the sales you can change it to sum up instead of count it.

HTH

 

[rc-alex]

Hi Guus,

Thanks but I'm not sure I understand.

I already have a query that shows the values (the 5,000; 4,000; etc.) that should be displayed:

Several rows will match each clerk on both the DepartmentID (report section) and ClerkID (report subsection).
When the row matches the ClerkID under which it resides, and the DepartmentID above it, I need it to be included in the bar graph. And only as long as the ClerkID and DepartmentID match those under which it is contained.

Thanks,
Alex

 

[rc-alex]

This is solved. Thanks!!