Generating Totals for Multiple Fields in a Record

[mandydawson]

Good Afternoon,

I'm attempting to create a database that records violations on driver logbooks. There is one log/record per day per driver. There are 11 violation categories, and 85+ violations. Right now, each violation is its own field with Y or N radio buttons (Y=1 and N=0).

I need the simplest way to be able to create a report totaling the number of violations per catergory in the record, and number of violations total for the record.

[Vaughan]

There should be a related table for VIOLATIONS related to each LogDay record, not separate fields.

Creating your report will then be as simple as making a summary report of the Violation records.

Welcome to relational database design. :D

[Lee Smith]

Hi Mandy, and welcome to the forum,

One of the great things about the FMForums, is that you can attach a files which can help us assist you sometimes quicker or you can make a mockup of your file, just be sure that it accurately represents the problem. Also, you'll want to ensure whatever file you used if you have removed any confidential information from them.

Once you have selected a method, then just follow these steps.

Step 1

Find the file on your hard drive, and zip it.

Step 2

Do not use the Quick Reply, BUT instead, choose the More Reply Option

Step 3

Click on Attached Files and this will take you to your hard drive to locate your file that you want to attach and that you previously zipped. Select the file, and then click Attach This File, and then click Add Reply.

If you should have a problem write me a private message.

Lee

[jkluchnik]

Hi Mandy,

Vaughan is absolutely right. Given the power of the relational database, you would have MUCH more flexibility, i.e., begin able to enter the same violation more than once in a record, and much more power in your reporting.

Having said that, based on what I can see from the layout you posted as an image, you could sum up the groups totals and then full total as follows:

Assuming 2 violation categories and 3 different violations per category, you would need 2 category total fields, and one full total field.

Category 1 Total = PatternCount ( List ( Violation 1; Violation 2; Violation 3 ) ; "Y" )

Category 2 Total = PatternCount ( List ( Violation 4; Violation 5; Violation 6 ) ; "Y" )

Total = Sum ( Category 1; Category 2 )

(As I don't have your sample file, I don't know how you have Y equaling 1. Therefore I am assuming that the Y and N are just text values. If in fact you have a field , call it Violation X Value, that equals 1 when the violation is Y, then you could substitute the Category X Total formulas with Sum ( Violation 1 Value; Violation 2 Value), etc...

See attached file.

Please note: A downside to this approach is that your report will then list all the violation categories, even when their value is zero. With Vaughan's approach, your reports would be much more concise, showing only actual violations. However, you may actually WANT the report showing the zero values. I know in my business, we have situations where our auditors want to see an affirmative "zero", which shows an intentional entry of the negative. In a case like that, your approach may be exactly what is desired.

Jorge

mandy.fp7.zip

[mandydawson]

See attached.

Logs.zip