summarising text data

  • Thread starter Thread starter Dan Welch
  • Start date Start date
D

Dan Welch

Good morning,
I use an Access database (Access 2000) at work and want to use it to produce
reports. The report I want to generate is based on our suppliers - whether
they are 'approved', 'approval pending' or 'not approved'.
I would like to have a part of the report stating 'total number of
suppliers: 100. 80 Approved, 15 approval pending, 5 not approved' but for
the life of me I can't get the program to calculate these figures.
I know how to do statistics for a number field but can't get the program to
summarise text fields.
Any ideas?
All help appreciated, even if it's just a pointer to the correct function in
the Help file!
cheers
dan
 
Dan,

What you can do is create a subreport for each
calculation that you want. In the queries for each of
those subreports, you are going to use the same fields
that you use for to specify criteria upon as you use for
your main report, and besides those fields, you are only
going to use the one field that you are concerned with for
getting a subreport calculation.

Lets say you are creating the subreport for the
Approved field and you are in design mode of your new
query. You would place the Approved field in your query,
and place the fields that you need to specify criteria
upon (if any) in the query. You would then select the
totals option at the top of your screen (it looks like a
funny looking large E). After you select that option, the
query design grid (screen) will show rows for Total in
each column. Now, what you are going to do is if you have
any fields that you had placed in the query to have
criteria placed upon them, place the criteria in the
Criteria specification for each of them. Now, in the
column of the query that holds the field (we are using the
Approved field as an example), in the Criteria for that
field you will place True. Also in that Approved field,
in the Total row, you will select Count. Now, make sure
the Approved field is on the complete left of the query
(to the left of the other field containing columns). In
the other field containing columns, in their Total row,
select Where. Now save the query, and open it. It will
show you the total of the Approved records for the
Approved field used in your query.

You can do almost the same thing for your other
fields that you are concerned in getting calculations
for. Remember, one query per calculation, do not try to
use one query for a group of your field calculations
because it will not work.

Then create subreports for each of your queries, and
place them in your main report as you see required. You
can size the subreports to be very small, placing the
field that was calculated upon in the upper left corner of
the subreport and eliminating any headings or footers.
Then after placing them on the main report, you can size
the part of the main report that holds them to be the size
you desire with adjustment.

This is not a long process, it is just step by step,
and really works.

I hope I was of some help.

Casey
 
Thanks Casey. I followed your instructions, found them really complicated
but persevered... then something clicked and it's all clear - if a little
complicated! Much appreciated, makes my life easier... thought it was a
little absurd having all this information on a database and totting columns
of figures up on a piece of paper!
cheers
dan
 
Back
Top