Summing Records

[JeepsR4Mud]

I have a table called Staff Injuries.

Each record is linked to a primary table through two fields: site and week, both of which are primary keys. (Thanks to someone for suggesting a compound primary key).

This is a secondary table because it can have more than one answer.

While the record details the incident, and I want to retain that information in a report, I'd like to sum the number of records per week (i.e. how many staff injuries did a side experience).

How can I do that?

Also, a couple of the fields are yes/no. How can I sum the number of yes answers? For example, the one field is workrelated (was the accident work related). I'd like to know how many of the accidents were directly related to job activities.

Thanks.

Gayle Ann

 

[Sohaila Taravati]

Make your report thruogh the wizard. The wizard will ask you if you want to group your data and that is what you actually want to do. For counting the yes fields, just go to search and you will find lots of info there.

 

[JeepsR4Mud]

Hello,

I tried that, but it doesn't work for this type of table.

The fields I need summed are yes/no, so it just lists YES for everything.

It doesn't tell me how many YES answers I have and and how many records total.

For example, assume I have 2 injuries, each with their own record. One is form someone in the kitchen who cut his finger. THe answer to workrelated would be YES. Someone else slipped getting out of his car. Such an injury is not directly work related, so the answer is NO.

I need the report to say I had 2 injuries, and one was work related.

Does that make sense?

Gayle Ann

 

[Sohaila Taravati]

Then do this:

=Sum(IIf([YourYes/NoField]= "Yes",1,0))
This will count your yeses.

 

[JeepsR4Mud]

Hello,

Do I use that expression in a query or in a unbound field in the report? I tried it both places and it doesn't work.

I've tried the help files, but... You need to know WHAT you are looking for before they are of use.

I am SOOOO frustrated. I know this is somethign simple, but I'm jsut not getting it.

Thanks.

Gayle Ann

 

[Rich]

There are no quotes on yes/no data fields
=Sum(Iif([MyField],1,0)) and Sum(Iif([MyField]=No,1,0)) resp.

 

[JeepsR4Mud]

Error message

I got the following error message when I used it in the query:

You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)

You tried to execute a query that does not include the specified expression as part of an aggregate function or grouping.

Possible cause:

You did not enter an aggregate function in the TRANSFORM statement.

 

[JeepsR4Mud]

Where is the Yes/No help section?

Thanks.

Gayle Ann

 

[Rich]

Use unbound textboxes on the Report

 

[JeepsR4Mud]

Rich,

Bless you!

You are my new hero in life!

Gayle Ann