Solved Count Records in Group Footer

[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))

 

[Tophan]

What indicates a record is not a Revision?

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

Or

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

Hi. The revision box of the first edition of a drawing is usually left blank by the design consultant, however, for it to show in the report, I enter a dash ( - ) in the database to indicate that this was the first drawing. If I don't enter anything in the field, it will not show in the report. The reason I wanted to count the drawings listed per consultant in this specific report is to use the count as a means of cross-checking that I have entered details for every drawing issued.

I have a query already that is based on the tblDwgReg which counts all the drawings issued per consultant, but if I miss adding details to the tblDwgRegDtls table, this count would help me see that I missed something under that consultant.

 

[DHookom]

I have a query already that is based on the tblDwgReg which counts all the drawings issued per consultant, but if I miss adding details to the tblDwgRegDtls table, this count would help me see that I missed something under that consultant.

If you already have the number you are looking for in another query, why not add that query to your report's record source? Join on the appropriate fields so you don't end up with more records than the original record source.

 

[June7]

Why would record not show if field is Null?

 

[Ken Sheridan]

The revision box of the first edition of a drawing is usually left blank by the design consultant, however, for it to show in the report, I enter a dash ( - ) in the database to indicate that this was the first drawing.

I would favour modelling Editions by a separate table referencing a Drawings table. That way attributes which are common to all editions are recorded only once, in a row in the Drawings table, whereas attributes which are not common to all editions are recorded in rows in the Editions table. The primary key of Editions would be a composite of a foreign key DrawingNumber column and a non-key EditionNumber column. The latter can be easily numbered automatically from 1 upwards per DrawingNumber. The original would thus have an EditionNumber of 1, and each revision would be a value of 2 or more.

The attached file illustrates how, if necessary, each row in Drawings can be forced to have at least one referencing row in Editions inserted by means of a subform. In this little demo the example used forces each family to have at least one family member inserted.