Totals in a Report 1

[JBuckley2000]

I think this is a very simple question maybe:

On one report, I want to be able to give the total number of records that are in the report at the end of all of the records for a specific product I am looking at.

On another report, I ask for certain information for any product, not just a specific one. Is there a way to report totals for each product individually?

Thanks, JSF

Jason Facey
jfacey@lithonia.com

 

[Rohdem]

It sounds like you need to set up groupings on the report with a group footer that gives you subtotals for each product.

Mike Rohde
rohdem@marshallengines.com

 

[JBuckley2000]

Yeah that is what I want to do, but I don't know how to do it, any suggestions? JSF

Jason Facey
jfacey@lithonia.com

 

[Rohdem]

Probably the easiest way to do it is to use the report wizard and let it set them up for you. To do it yourself, go into design view and click the sorting and grouping button which is to the right of the toolbox button and to the left of the autoformat button. Select your product field and select ascending or descending for the sort order. At the bottom of the dialog box, set the group footer to yes. Close the dialog box and you should have another section on your report called product footer. This section will appear once for each group that you have. If you use sum or count functions in this group, it will perform the operations only on the group.

Good luck!

Mike Rohde
rohdem@marshallengines.com

 

[JBuckley2000]

Yeah I was doing that also, but I was unsure on how to set up a sum or count function...when I used it, it was adding it to my parameters for when I opened the report and it asked me to enter in something...I know that is not right. I sure hope you can help me out one more time... JSF

Jason Facey
jfacey@lithonia.com

 

[Rohdem]

Create a blank text box, in it put one of these statements in its control source:

=count([somefieldname])

or

=sum([somefieldname])

Mike Rohde
rohdem@marshallengines.com

 

[JBuckley2000]

Well I actually had one more question...for another report I am doing, instead of breaking up the products into a specific defect, I am asking to list all defects, no matter what...is there a way to give totals for each defect that comes up on the report even though they under the same field? JSF

Jason Facey
jfacey@lithonia.com

 

[Rohdem]

If I am understanding you correctly, you have a 'defect' field and you want to count the number of occurences of each type of defect. This could be done by with a query or using the DCOUNT function.

To do it in a query, click the totals button in the toolbar (it is next to the show table button). Add the defect field to the query grid. In the total row under the defect field, select 'group by'. Add the defect field to the query grid a second time and this time in the total row select 'count'. Run the query.

To do this by using dcount, the syntax would be:

=dcount("defect", "tablename", "[defect] = 'leaking oil'&quot

*this example assumes the field name is 'defect' and the defect that you want to count is 'leaking oil'

Place this function in a text box on your report.


Mike Rohde
rohdem@marshallengines.com

 

[JBuckley2000]

I think that should do it...I think I also have to assume my specific table name also, right? THank you so much for being so fast and so very helpful...this saved me a ton of headaches and time!! You got my vote of course!! JSF

Jason Facey
jfacey@lithonia.com

 

[JBuckley2000]

Hey Mike,

For some reason, I am coming up with an Error even though I know that there are defects within this report...do you know what I might be doing wrong? I am completely lost now. Why would it not be totaling the defects? JSF

Jason Facey
jfacey@lithonia.com

 

[Rohdem]

Jason

Are you using the dcount statement or a totals query?
If it is a dcount statement, can you post the syntax you are using. If you want, you can e-mail me your database and I could take a look at it.

The syntax of the dcount statement can be a little tricky. Single quotes, double quotes, ampersands, its all very confusing!
Mike Rohde
rohdem@marshallengines.com

 

[JBuckley2000]

Hey Mike,

Thanks for being so considerate. Here is a little of what I got. By the way, my database is 14 megs so can I still send it to you? Maybe that is too large.

Example:

=DCount("DEFECT CODE","Sum2-By Prod Date and All Defect Codes","DEFECT CODE='01'&quot

I did it with brackets also and it still did not work. The very long title is the name of the query that my report is based on and what causes the parameters to show up---you can email me also if that is easier. Thanks, JSF

Jason Facey
jfacey@lithonia.com