Count Records in Group Footer (2 Viewers)

[Tophan]

Good afternoon.

I have a drawing register report which lists each drawing for a project and the revisions per drawing. I tried adding a count of the records per design consultant in the group header to show the total number of drawings issued per consultant (not counting the revisions per drawing). The count records in the footer section is also counting the number of times a drawing has been revised - so if I have a drawing that has been revised 4 times it will return a count of 4 instead 1.

For example, the structural engineer has issued 37 drawings however, the group footer is returning a count of 59 which includes all the revisions.

Right now, the only way I am getting the correct figure is by running a query counting all the drawings per consultant and adding a Dlookup formula in the footer section for the ConsultantID on the report.

Is there a better way to count the records in the report?

 

[tvanstiphout]

You can count records in a report without DLookup if you're counting in its RecordSource.
It seems that in your case the main report shows drawings (which you can count using Count), not their revisions, so you cannot count revisions with this recordsource. DCount or DLookup works, as does putting a subreport (bound to a Totals query) in the footer.

 

[DHookom]

I would create a totals query that counts the drawings you want counted by the engineer field. Then, add this query to your report’s recordsource, joining on the engineer fields. This will expose the count field in your report’s fields.

 

[Tophan]

I would create a totals query that counts the drawings you want counted by the engineer field. Then, add this query to your report’s recordsource, joining on the engineer fields. This will expose the count field in your report’s fields.

Thank you. That's what I've done, I was just wondering if there was another way to count the records within the report.

 

[Tophan]

I would create a totals query that counts the drawings you want counted by the engineer field. Then, add this query to your report’s recordsource, joining on the engineer fields. This will expose the count field in your report’s fields.

You can count records in a report without DLookup if you're counting in its RecordSource.
It seems that in your case the main report shows drawings (which you can count using Count), not their revisions, so you cannot count revisions with this recordsource. DCount or DLookup works, as does putting a subreport (bound to a Totals query) in the footer.

Thank you. Your explanation helped me understand why I was getting the incorrect count as the recordsource, which is a query, would have the drawing listed every time it was revised. I will keep the Dlookup to the query I did previously counting the number of drawings per consultant.

 

[Mark_]

Thank you. That's what I've done, I was just wondering if there was another way to count the records within the report.

For counting, you can add two expressions to your query. Both are "If" statements that will return 1 or 0 depending on if this is an original drawing or revision. Not sure on the logic you will need to differentiate. You would then have a sum in your group footer for each consultant.

This approach normally works best when you have multiple different criteria you need to count per record and you don't have need for these totals elsewhere. DHookom's solution works better when you have more than one place you need to return these same results.

 

[theDBguy]

Thank you. That's what I've done, I was just wondering if there was another way to count the records within the report.

Not in front of a computer now, so not sure if this approach will work for you, but have you tried using the RunningSum Over Group property yet?

 

[Tophan]

Not in front of a computer now, so not sure if this approach will work for you, but have you tried using the RunningSum Over Group property yet?

No I haven't. Will try when I get home and let you know if it worked.

 

[Tophan]

For counting, you can add two expressions to your query. Both are "If" statements that will return 1 or 0 depending on if this is an original drawing or revision. Not sure on the logic you will need to differentiate. You would then have a sum in your group footer for each consultant.

This approach normally works best when you have multiple different criteria you need to count per record and you don't have need for these totals elsewhere. DHookom's solution works better when you have more than one place you need to return these same results.

I'll try this too!

 

[Ken Sheridan]

The following is an example of a query which returns a count of distinct values, in this case employers of contacts per city. It does this by means of a subquery in the outer query's FROM clause. The subquery includes the DISTINCT predicate to return only one instance of each each set of CityID, City,Region, and EmployerID values. The outer query groups the rows by City, Region, and CityID values and returns the count of rows for each group. Without the DISTINCT predicate the count of contacts employed per employer per city would be returned. Contacts are thus analogous to revisions in your case, with employers being analogous to drawings, and cities analogous to projects.

SELECT City, Region, COUNT(*) AS EmployerCount
FROM (SELECT DISTINCT Cities.CityID, City, Region, EmployerID
             FROM Regions INNER JOIN ((Cities INNER JOIN Contacts
             ON Cities.CityID = Contacts.CityID) INNER JOIN ContactEmployers
             ON Contacts.ContactID = ContactEmployers.ContactID)
             ON Regions.RegionID = Cities.RegionID)
GROUP BY City, Region, CityID;

 

[arnelgp]

another suggestion.
first create a query that will Group Consultant and DrawingNo:

Select Consultant, DrawingNumber From YourTable Group By Consultant, DrawingNumber;

name the query qryGrpConsultantDwg.

Next use DCount on your Report as the ControlSource of your GroupTotal (on header):

=Dcount("1", "qryGrpConsultantDwg", "Consultant = '" & [ConsultantTextboxOnReport] & "' And DrawingNumber = '" & [DrawingNumberTextbox] & "'")

 

[June7]

What indicates a record is not a Revision?

=Count(IIf(fieldname = "Original", 1, Null))

Or

=Sum(IIf(fieldname = "Original", 1, 0))