Dividing in Access

[Gale Coleman]

Hello all,

I am using Access 2000 on Windows 2000 machines.
I have a Page Header that has the following in it:

Case number, Case handler, time in case, first name, last name, and reason.

I have a CaseNum Header that has the following in it:

CaseNum, SNum

I have Detail that has the following in it:

SumOfTTIME, SFname, SLname and rClosed

I have a footer called CASENUM Footer that has the following in it:

="Summary for " & "'CASENUM' = " & " " & [CASENUM] & " (" & Count(*) & " " &
IIf(Count(*)=1,"detail record","detail records") & ")"

and =Sum([SumOfTTIME])

I have a page footer that gives me the date and time.

I also have a Report Footer that has the following in it.

=Sum([SumOfTTIME])

which gives me the total number of hours worked on a case for all the cases
that are showing.

What I am now needing is to take the =Sum([SumOfTTIME]) and divide it by the
number of cases showing.

Is there a way to do this?

(I hope I gave enough information)

Gale

 

[John Vinson]

What I am now needing is to take the =Sum([SumOfTTIME]) and divide it by the
number of cases showing.

Is there a way to do this?

Try setting the control source of a textbox to

=Sum([SumOfTTIME]) / Count(*)

 

[Gale Coleman]

Nope, that didn't work. It gave me the same amount as the average did. It
is dividing the number of time slips by the sum of TTIME instead of dividing
the number of cases by the sum of TTIME.

Gale

 

[John Vinson]

Nope, that didn't work. It gave me the same amount as the average did. It
is dividing the number of time slips by the sum of TTIME instead of dividing
the number of cases by the sum of TTIME.

Gale

It may have to do with the Sorting and Grouping settings - if you set
a separate textbox to =Count(*) what do you see? 1, or the number of
records displayed?

 

[Gale Coleman]

John,

I see the number of time records for each case listed which is 8. If I use
the avg thing, I get 4.4. Which is 35.2 divided by 8. What I wanted was a
way to divide the number of cases showing (3) by the total hours which is
35.2.

I am having problems getting the number of cases showing (3) instead of the
number of time records (8).

Gale

 

[John Vinson]

John,

I see the number of time records for each case listed which is 8. If I use
the avg thing, I get 4.4. Which is 35.2 divided by 8. What I wanted was a
way to divide the number of cases showing (3) by the total hours which is
35.2.

I am having problems getting the number of cases showing (3) instead of the
number of time records (8).

I guess I don't understand what you mean by "the number of cases
showing". Showing on a particular page of the report? showing on the
report as a whole? What's the Recordsource of the report? What are you
"showing", and how?

 

[John Vinson]

What I did was create a report from the query where it would show the time
records (TTIME), the name of the person that put the time records in
(SMEMBER.SFNAME, SMEMBER.SLNAME), and their staff Number (CLIENTS.SNUM), the
amount of time they input (TTIME) and the reason closed (CLIENTSW.RCLOSED)
for each case (CLIENTSW.CASENUM).

Try putting a textbox, txtRunCount, in the Casenum header or footer
(it can be invisible); set its Control Source to =1 and its Running
Sum property to Over Group.

Use [txtRunCount] as the count in the expression in the report footer.

I'm not *certain* this will work but give it a try! If not, you may
need to use a DCount() to count records in the table.

 

[Gale Coleman]

Thanks John,

That worked like a charm! Exactly what I needed.

Thanks again,

Gale