Report Question

[Stockwell43]

Hello,

I am working on a Procedure database that has a sub form. That database,
form, report and queries all work fine (so far). On one report I have a graph
that show the for departments each with a colored bar to show how many
procedures they have completed. This works great and as I change the
information on the form the numbers change on the graph. What I want to do is
above the graph I would like four textboxes (one for each department) that
will show the number of procedures assigned to each department. If I make a
query and just place one department and run it it comes out with the correct
number of records. Do I need to make four queries, one for each department?
Sorry, I never created a report with a graph in it. I suppose I could
manually update a label box on the report when a new procedure is assigned
but thought there would be an easier way.

Thanks!!!

 

[Ken Sheridan]

You can create a single query, grouped by Department and counting the
procedures assigned. This will return four rows each with a separate
procedure count per department. You can then place a 4-column subreport
above the graph with its column layout set to 'across then down'. The
subreport will have a single control, a text box bound to the column
returning the count of procedures. This will give you the four values laid
out in a single line. You'll need to space the subreport's columns carefully
to line them up with the columns of the graph.

The other thing you'll need to make sure of is that the values are in the
same order as the bars of the graph. If you group the query by department
name the order will be that of the names sorted alphabetically. If you want
to impose a different order then you should add an extra integer number
column to the Departments table, DeptSortOrder say, indexed uniquely, in
which you can insert values from 100, 200, 300 and 400 in the order you want
the departments sorted. Then group the query by this column, not the
department name column. The reason for using multiples of 100 is that, in the
event of any new departments being created you can easily insert intermediate
values in the sequence.

Ken Sheridan
Stafford, England

 

[Stockwell43]

Hi Ken,

This sounds like what I need. I'll give it a go this morning.

Thank you for your replay, most appreciated!!!

 

[Stockwell43]

Hi Ken,

I must be doing something wrong because my query shows each department and
the number of procedures assigned but when I run the report it's blank. And I
followed the instruction in MS Help for Creating a multiple column report and
I get nothing.

Here is what I have in the query:

SELECT tblproceduresub.Department, Count(1) AS ["Number of Procedures"]
FROM tblproceduresub
GROUP BY tblproceduresub.Department;

It gives me two columns one showing the departments and the other showing
the number of procedures assigned to each department. (maybe I messed up the
query)

In the page setup of the report I clicked on the Columns tab and have the
following:
Number of columns 4
Row Spacing 0
Column Spacing .5

Column Size:
Width .5 and Height .3

Column Layout:
Across, then Down

I tried each text box separately and then both together in the details
section and it's blank. Now the Number of Procedures box does show the Quotes
but I'm not sure if that matters. Could you please tell me what I did wrong?
Should it have been placed in the main report first? I tried testing it by it
self to see if the data was correct and format it.

Thanks Ken!!

 

[Stockwell43]

I got it. I had the field in the middle of the Details section. I just moved
it all the way to the left and every thing shows. Sorry about that but this
was my first time doing a report like this but it's a great learning
experience.

Thank you for your help!!!!!