counting records

[diggadave]

Hi Guys

I am trying to produce a report on a query, which will count the records the query holds.

I know that I can get the total by using =Count("*&quot.

What I would like to also do, is get a count of each of the record types.

Record types range from numbers 1 to 10, and i would like to be able to display the number of record number 1, number 2 etc

I've heard about DCount, but not 100% positive how to implement it and if it will work for my requirements.

I will try and give an example of what I mean by using a mock sql statement, if anyone isn't getting what I mean.

=Count("*&quot where infoheading="1"

Would be very grateful for any help!!!

Best Wishes

Digga

 

[shannonp]

Maybe make another query grouped on 'record type'. Then simply add a Count. Once this is accomplished it should be a simple matter of adding an unbound sub-report (with no links to main report).

 

[ReluctantDataGuy]

Okay, this gets a little complicated - but lets give it a try.

First you have a field containing the record type, which is a number from 1 to 10.

Place 10 extra fields in the detail section of the report - one for each record type. These are HIDDEN fields, so they won't clutter things up except in design mode.

If each of these 10 fields, the record source will be:

IIf([RecordType] = 1, 1, 0)
IIf([RecordType] = 2, 1, 0)
IIf([RecordType] = 3, 1, 0)
and so on for each of the text boxes.

On each of these textboxes there will be a property called RunningSum. Set all these text boxes to Yes for Running Sum.

At the bottom of the report (ie: report footer), add another set of 10 text boxes and set the record source of each of those text boxes to equal the the appropriate text box from the detail section.

Since the detail section text boxes are "Running Sum", at the end of the report the form footer text boxes will show the result of summing the 1s and 0s from each record.

On a large table, this would be WAY faster than using DCount() tem times in your report.

As I said, its a little messy and a little complicated, but you should be able to get it to work.

 

[BlueMonkey]

Would a crosstab query not produce the goods?

B.M.

 

[ReluctantDataGuy]

A crosstab doesn't help much if you want totals in both vertical and horizontal directions, since they only give totals horizontally.

In a report, summing values in each record is a vertical sum, so no, in this case I don't think a crosstab would work.

If the report was turned on its side, and that works for the users, maybe...

 

[RemyS]

You mentioned that you were trying to produce a Report.

In design view:

First apply a Grouping to the report based on your record type: Goto View > Sorting and Grouping

Then Add you record type field to the list, and select Yes for the Group Footer (or header if you want the count at the top. You should now see a new detail type section in the report.

Put your Count("*&quot textbox into this section.


This will give you a grouped report with the record type Counts at the end/begining of each record type group details.


If that's still not what you're looking to achieve then another alternative would be to try using Sub-queries/Sub-reports (as ShannonP1 suggested).

Hundreds of ways to do things with PC's, and learning new ways every day.