Creating A Report With Averages/Calculated Totals

[Walk_The_Line]

So now I'm trying to create a report, but with some totals.


One of them is trying to calculate the SUM of the Time Used on a Question. I tried using sum, but I gather that the format I'm using (5:00) is throwing off access.

Anybody know how to implement that?

Thanks

 

[Rabbie]

One of them is trying to calculate the SUM of the Time Used on a Question. I tried using sum, but I gather that the format I'm using (5:00) is throwing off access.

Anybody know how to implement that?

Thanks

Are you trying to sum the duration of times to answer the question? If so You need to subtract StartTime from FinishTime. This will be in Date/time format ie the time will the fractional part where 1 = 24 hours. You should be able to use the Format command to display it meaningfully.

If you have already converted your duration to character form then you can't use SUM. You will need to go back to the actual duration field if you want to SUM it.

 

[Walk_The_Line]

I have different questions from different funds that need to be generated into a report.

1) How long does it take Fund1 to answer questions (on average)
2) How long does it take Fund2 to answer questions (on average)

things like that - I need to generate for reports. I have tried everything to no avail.

 

[Rabbie]

How do you get the numbers you are trying to SUM. What format are they in. Can you post a cut down version of your DB so we can see what data is actually there.

 

[Walk_The_Line]

Thats the format.

 

[Rabbie]

It looks like you have some blank values in the field which will show up as null so you will need to wrap them in a NZ function. If the durations are stored as times then SUM should now work.

It will need to look like SUM(NZ(duration))

 

[Walk_The_Line]

So I just insert the SUM(NZ(duration)) into the control source?

 

[Rabbie]

So I just insert the SUM(NZ(duration)) into the control source?

In the places where you had Sum(duration) before. That should at least get rid of the Null problem.

 

[Walk_The_Line]

OK - now that I've updated the blank fields with information, the process still hasn't worked yet.